语句优化,该怎么解决

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 backupzhuangkleft 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.lbleft join cqglon  backupzhuangk.kb=cqgl.kb and backupzhuangk.jz=cqgl.jz and backupzhuangk.lb=cqgl.lbwhere '"+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
------解决方案--------------------