SQL语句优化问题

[education520]

现在有2个表，排班信息表A，和考勤信息表B
A表存储的是每个人每天的排班情况，B表存的是每个人每天的打开记录
现在要做一个报表，统计每个人每天每个班次是否迟到早退，他们是多对多关系，统计时间一旦扩大，查询速度就很慢，请问如何统计比较好？
表结构如下：
A表语句：
select b.mastid as 单位ID,b.mastname as 单位名称,b.empid as 员工ID,b.empname as 员工名称,
a.classid as 班次ID,a.classname as 班次名称,b.stockyear as 年,b.stockmonth as 月,b.stockday as 日,
c.startclock1 as 上班开始打开时间,beganwork as 上班时间,c.endclock1 as 上班结束打卡时间,
c.startclock2 as 下班开始打卡时间,endwork as 下班时间,endclock2 as 下班结束打卡时间
from Att_ClassTYDBChild a inner join Att_ClassTYDB b on a.mid=b.id left join att_classsub c
on a.classid=c.id left join ed_employeedata d on b.empid=d.empid LEFT JOIN dbo.ED_JOBINFO g ON d.EMPID = g.JI_EID
left join br_mastcustomer e on d.community=e.id
数据如下：

B表语句：
select empno as 员工ID,brushdatetime as 打卡时间 from att_record
数据如下：


我的查询语句如下：
select max(shortname) as 单位名称,empid as 员工编号,max(empname) as 员工姓名,
convert(char(4),stockyear)+'-'+right('0'+rtrim(convert(char(2),stockmonth)),2)+'-'+right('0'+rtrim(convert(char(2),stockday)),2) as 考勤日期,
max(classname) as 班次名称,Max(sbTime) as sbTime,Max(xbTime) as xbTime,
(case when substring(min(aclockval),1,2)='AA' then substring(min(aclockval),4,19) when substring(min(aclockval),1,2)='AB'
then substring(min(aclockval),4,19) when substring(min(aclockval),1,2)='AC' then '' else '' end) as 上班打卡时间,
(case when substring(min(aclockval),1,2)='AA' then '正常上班' when substring(min(aclockval),1,2)='AB' then '上班迟到'
when substring(min(aclockval),1,2)='AC' then '上班未打卡' else '' end) as 上班考勤结果,
(case when substring(min(bclockval),1,2)='BA' then substring(min(bclockval),4,19) when substring(min(bclockval),1,2)='BB'
then substring(min(bclockval),4,19) when substring(min(bclockval),1,2)='BC' then '' else '' end) as 下班打卡时间,
(case when substring(min(bclockval),1,2)='BA' then '正常下班' when substring(min(bclockval),1,2)='BB' then '下班早退'
when substring(min(bclockval),1,2)='BC' then '下班未打卡' else '' end) as 下班考勤结果
from
(select b.stockyear,b.stockmonth,b.stockday,e.id as mastid,e.shortname,d.empname,d.empid,c.id as classid,dbo.JsDelTime(c.beganwork,c.LateTime) as sbTime,
dbo.JsAddTime(c.endwork,c.LateTime) as xbTime,c.classsubname+c.obligate1+'['+dbo.JsDelTime(c.beganwork,c.LateTime)+'-'+dbo.JsAddTime(c.endwork,c.LateTime)+']'
as classname,(case when f.brushdatetime>=dateadd(n,-dbo.JSMinites(startclock1,beganwork),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),
b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ beganwork)) and
f.brushdatetime<=dateadd(n,dbo.JSMinites(beganwork,endclock1),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' ' + beganwork)) and
f.brushdatetime<=convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ c.beganwork)
then 'AA&'+convert(varchar(30),f.brushdatetime,120)
when f.brushdatetime>=dateadd(n,-dbo.JSMinites(startclock1,beganwork),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ beganwork))
and f.brushdatetime<=dateadd(n,dbo.JSMinites(beganwork,endclock1),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' ' + beganwork))
and f.brushdatetime>convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ c.beganwork) then 'AB&'+convert(varchar(30),f.brushdatetime,120)
else 'AC&' end ) as aclockval,
(case when f.brushdatetime>=dateadd(n,-dbo.JSMinites(startclock2,endwork),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ endwork)) and
f.brushdatetime<=dateadd(n,dbo.JSMinites(endwork,endclock2),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' ' + endwork)) and
f.brushdatetime>=convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ c.endwork) then 'BA&'+convert(varchar(30),f.brushdatetime,120)
when f.brushdatetime>=dateadd(n,-dbo.JSMinites(startclock2,endwork),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ endwork)) and
f.brushdatetime<=dateadd(n,dbo.JSMinites(endwork,endclock2),convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' ' + endwork))
and f.brushdatetime<convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+rtrim(convert(char(2),b.stockday))+' '+ c.endwork) then 'BB&'+convert(varchar(30),f.brushdatetime,120) else 'BC&' end ) as bclockval
from Att_ClassTYDBChild a inner join Att_ClassTYDB b on a.mid=b.id left join att_classsub c on a.classid=c.id left join ed_employeedata d on
b.empid=d.empid LEFT JOIN dbo.ED_JOBINFO g ON d.EMPID = g.JI_EID left join br_mastcustomer e on d.community=e.id
left join (select empno,brushdatetime from EpowerHYL_ZK..att_record where brushdatetime>=convert(datetime,'2017-10-23 21:00:00') and
brushdatetime<convert(datetime,'2017-10-25 03:00:00') and empno in(select empid from ed_employeedata where community=10044)) as f
on d.empid=f.empno where 1=1 and
convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+convert(char(2),b.stockday))>=convert(datetime,'2017-10-01')
and convert(datetime,convert(char(4),b.stockyear)+'-'+rtrim(convert(char(2),b.stockmonth))+'-'+convert(char(2),b.stockday))<convert(datetime,'2017-10-24')
and StartClock1<>'00:00' and EndClock1<>'00:00' and d.community=10044 ) as t group by mastid,empid,classid,stockyear,stockmonth,stockday
需要显示的结果如下：

[摩西云]

建议设计几个view临时表视图吧，现在这样的sql太复杂，也不利于维护，通过视图+存储过程，sql结构会简化很多

[OwenZeng_DBA]

通过使用临时表，吧语句进行拆分，简化，这样的语句过于复杂可读性差 也不利于数据库产生最优的执行计划