这个sql有点长,大家来看看
写了个查询，统计加班工时的，相信CSDN上有很多朋友都碰到过这样的问题，
就是觉得有点长了，有点麻烦，用的是VB+Seagate Crystal Reports ,每次都要5到10秒左右,大家讨论下，用什么方法简化下(版面有限，可能看起来有点费劲)
SELECT SEC,PNL,NAME,INDATE,WK,WK_USE,WK_EUSE,WK1,WK2,WK3,WK4,WK5,WK6,WK7,WK8,WK9,WK10,WK11,WK12 FROM
(SELECT B.SEC,B.PNL,B.NAME,B.INDATE,C.WK,
(SUM(A.WK_OVER)+SUM(A.WK_HOLIDAY)+SUM(CASE WHEN A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END ) ) AS WK_USE,C.WK-(SUM(A.WK_OVER)+SUM(A.WK_HOLIDAY)+SUM(CASE WHEN A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END ) ) AS WK_EUSE,
WK1=(SUM(CASE WHEN A.CAR_D BETWEEN '20070101 ' AND '20070131 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070101 ' AND '20070131 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070101 ' AND '20070131 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )),
WK2=(SUM(CASE WHEN A.CAR_D BETWEEN '20070201 ' AND '20070231 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070201 ' AND '20070231 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070201 ' AND '20070231 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK3=(SUM(CASE WHEN A.CAR_D BETWEEN '20070301 ' AND '20070331 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070301 ' AND '20070331 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070301 ' AND '20070331 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK4=(SUM(CASE WHEN A.CAR_D BETWEEN '20070401 ' AND '20070431 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070401 ' AND '20070431 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070401 ' AND '20070431 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK5=(SUM(CASE WHEN A.CAR_D BETWEEN '20070501 ' AND '20070531 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070501 ' AND '20070531 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070501 ' AND '20070531 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK6=(SUM(CASE WHEN A.CAR_D BETWEEN '20070601 ' AND '20070631 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070601 ' AND '20070631 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070601 ' AND '20070631 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK7=(SUM(CASE WHEN A.CAR_D BETWEEN '20070701 ' AND '20070731 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070701 ' AND '20070731 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070701 ' AND '20070731 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK8=(SUM(CASE WHEN A.CAR_D BETWEEN '20070801 ' AND '20070831 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070801 ' AND '20070831 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070801 ' AND '20070831 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK9=(SUM(CASE WHEN A.CAR_D BETWEEN '20070901 ' AND '20070931 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070901 ' AND '20070931 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20070901 ' AND '20070931 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK10=(SUM(CASE WHEN A.CAR_D BETWEEN '20071001 ' AND '20071031 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20071001 ' AND '20071031 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20071001 ' AND '20071031 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK11=(SUM(CASE WHEN A.CAR_D BETWEEN '20071101 ' AND '20071131 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20071101 ' AND '20071131 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20071101 ' AND '20071131 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END )) ,
WK12=(SUM(CASE WHEN A.CAR_D BETWEEN '20071201 ' AND '20071231 ' AND A.IWEEK= '7 ' THEN A.WK_STAND ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20071201 ' AND '20071231 ' THEN A.WK_OVER+A.WK_HOLIDAY ELSE 0 END )+SUM(CASE WHEN A.CAR_D BETWEEN '20071201 ' AND '20071231 ' AND A.IWEEK= '1 ' THEN A.WK_STAND ELSE 0 END ))
FROM WORKTIME A LEFT OUTER JOIN PERSON B ON A.FACT_NO=B.FACT_NO AND A.PNL=B.PNL LEFT OUTER JOIN (SELECT FACT_NO,PNL,NAME,WK=CASE WHEN YEAR(INDATE)= '2006 ' THEN '432 ' ELSE ((datediff(dd,INDATE,dateadd(day,-1,convert(char(8),dateadd(month,1,INDATE),120)+ '1 '))-(7-datepart(weekday,INDATE)))/7+1)*8+(12-MONTH(INDATE))*36 END FROM PERSON ) C
ON A.FACT_NO=C.FACT_NO AND A.PNL=C.PNL WHERE LEFT(A.CAR_D,4)= '2007 '
GROUP BY B.SEC,B.PNL,B.NAME,B.INDATE,C.WK) A WHERE A.WK_EUSE> 0 ORDER BY SEC,PNL
[解决办法]
哇,这么长的SQL语句,牛人啊.
执行5到10秒也难免.
建议用临时表,或者把查询中的复杂计算先算出来,为表加一新列.
如这个子查询
SELECT FACT_NO,PNL,NAME,WK=CASE WHEN YEAR(INDATE)= '2006 ' THEN '432 ' ELSE ((datediff(dd,INDATE,dateadd(day,-1,convert(char(8),dateadd(month,1,INDATE),120)+ '1 '))-(7-datepart(weekday,INDATE)))/7+1)*8+(12-MONTH(INDATE))*36 END FROM PERSON
[解决办法]
5到10秒速度应该算正常
