语句优化
求把下面的语句优化一下，谢谢。
里面很多含有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这个可以怎么优化？
SQL code
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
------解决方案-------------------- 没什么好优化的，可以做个视图，把公用的部分用;with cte来代替好看的。
------解决方案--------------------SQL code
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
SQL 分组查询有关问题
