select backupzhuangk.kb,backupzhuangk.jz,backupzhuangk.lb,backupzhuangk.总人数,cqgl.banbie,cqgl.banci,
        (select count(*) from cqgl where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' and backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb)as 报总出勤,
        (select sum(case when left(gh,1)='E' then 1 else 0 end)  from cqgl where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' and backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb) as 应该有正式工出勤, (select sum(case  when yy='事假'then 1 else 0 end ) from cqgl where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' and backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb) as  事假,
        (select sum(case  when yy='年休'then 1 else 0 end ) from cqgl where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' and backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb) as  年休,
        (select sum(case  when yy='80%'then 1 else 0 end ) from cqgl where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' and backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb) as  百分之80,
        (select sum(case  when yy='病假'then 1 else 0 end ) from cqgl where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' and backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb) as  病假,
        (select sum(case  when yy='旷工'then 1 else 0 end ) from cqgl where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' and backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb) as  旷工  from backupzhuangk left join cqgl on  backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb where '"+TextBox1.Text+"'=convert(varchar(20),dateadd(HH,-8,backupzhuangk.记录时间),23) and  '"+TextBox1.Text+"'=convert(varchar(20),dateadd(HH,-8,cqgl.shijian),23) group by backupzhuangk.kb,backupzhuangk.jz,backupzhuangk.lb,backupzhuangk.总人数,backupzhuangk.记录时间,cqgl.banbie,cqgl.banci order by lb

select backupzhuangk.kb,backupzhuangk.jz,backupzhuangk.lb,backupzhuangk.总人数,cqgl.banbie,cqgl.banci,报总出勤,应该有正式工出勤,事假,年休,百分之80,病假,旷工
from backupzhuangk
left join
(
    select 
    count(1) 报总出勤,
    sum(case when left(gh,1)='E' then 1 else 0 end)应该有正式工出勤,
    sum(case  when yy='事假'then 1 else 0 end ) 事假,
    sum(case  when yy='年休'then 1 else 0 end ) 年休,
    sum(case  when yy='80%'then 1 else 0 end ) 百分之80,
    sum(case  when yy='病假'then 1 else 0 end ) 病假,
    sum(case  when yy='旷工'then 1 else 0 end ) 旷工
    from cqgl 
    group by kb,jz,lb
    where convert(varchar(20),dateadd(HH,-8,SHIJIAN),23)='" + TextBox1.Text + "' 
)x  
on  backupzhuangk.kb=x.kb and backupzhuangk.jz=x.jz and backupzhuangk.lb=x.lb
left join cqgl
on  backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lb
where '"+TextBox1.Text+"'=convert(varchar(20),dateadd(HH,-8,backupzhuangk.记录时间),23) 
and  '"+TextBox1.Text+"'=convert(varchar(20),dateadd(HH,-8,cqgl.shijian),23) 
group by backupzhuangk.kb,backupzhuangk.jz,backupzhuangk.lb,backupzhuangk.总人数,backupzhuangk.记录时间,cqgl.banbie,cqgl.banci 
order by lb


                         已经筛选出了前三名，在数据一样的情况下在筛选如何弄