相信大家都应该会碰见这样的问题。
求全年12月每个月每个单位的统计数量,为空的需要补全。也许表述得不是很清楚,下面把我的代码贴在这里就知道是怎么回事了
SELECT
B.UNITCODE,B.月份,ISNULL(A.上旬,0) AS 上旬,ISNULL(A.中旬,0) AS 中旬,ISNULL(A.下旬,0) AS 下旬
FROM
(
SELECT
UNITCODE,
MONTH(OPDATE) AS 月份,
SUM(CASE WHEN DAY(OPDATE)<=10 THEN 1 ELSE 0 END) AS 上旬,
SUM(CASE WHEN DAY(OPDATE)>10 AND DAY(OPDATE)<=20 THEN 1 ELSE 0 END) AS 中旬,
SUM(CASE WHEN DAY(OPDATE)>20 THEN 1 ELSE 0 END) AS 下旬
FROM
FLIGHT
GROUP BY
UNITCODE,MONTH(OPDATE)
) A
RIGHT JOIN
(
SELECT * FROM
(
SELECT DISTINCT UNITCODE FROM FLIGHT
) AS A
CROSS JOIN
(
SELECT NUMBER AS 月份 FROM MASTER..SPT_VALUES WHERE NUMBER BETWEEN 1 AND 12 AND TYPE='P'
)B
) AS B
ON
A.UNITCODE=B.UNITCODE AND A.月份=B.月份
ORDER BY
B.UNITCODE,B.月份
DB2的解决办法:
SELECT
B.UNITCODE,B.月份,NVL(A.上旬,0) AS 上旬,NVL(A.中旬,0) AS 中旬,NVL(A.下旬,0) AS 下旬
FROM
(
SELECT
UNITCODE,
MONTH(OPDATE) AS 月份,
SUM(CASE WHEN DAY(OPDATE)<=10 THEN 1 ELSE 0 END) AS 上旬,
SUM(CASE WHEN DAY(OPDATE)>10 AND DAY(OPDATE)<=20 THEN 1 ELSE 0 END) AS 中旬,
SUM(CASE WHEN DAY(OPDATE)>20 THEN 1 ELSE 0 END) AS 下旬
FROM
FLIGHT
GROUP BY
UNITCODE,MONTH(OPDATE)
) A
RIGHT JOIN
(
SELECT * FROM
(
SELECT DISTINCT UNITCODE FROM FLIGHT
) AS A
CROSS JOIN
(
SELECT ROW_NUMBER()OVER(ORDER BY (VALUES 1)) AS 月份 FROM SYSIBM.SYSCOLUMNS FETCH FIRST 12 ROWS ONLY
)B
) AS B
ON
A.UNITCODE=B.UNITCODE AND A.月份=B.月份
ORDER BY
B.UNITCODE,B.月份
很简单的代码,但是比较实用,就交给初学者看看吧。