---------------一个常见的项目中的问题（SQL SERVER和DB2）-------------

相信大家都应该会碰见这样的问题。

求全年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.月份

很简单的代码，但是比较实用，就交给初学者看看吧。