IBM DB2 对存储日期的表执行PIVOT行转列按年月统计实现

解决方案 #

如果你的DATE字段为字符串存储格式，使用如下SQL查询：

SELECT
  YEAR(TO_DATE(DATE, 'DD/MM/YYYY')) AS YEAR,
  COUNT(CASE WHEN MONTH(TO_DATE(DATE, 'DD/MM/YYYY')) = 10 THEN 1 END) AS OCTOBER,
  COUNT(CASE WHEN MONTH(TO_DATE(DATE, 'DD/MM/YYYY')) = 11 THEN 1 END) AS NOVEMBER
FROM 替换为你的实际表名
GROUP BY YEAR(TO_DATE(DATE, 'DD/MM/YYYY'))

如果DATE字段本身就是DB2的DATE日期类型，可简化为：

SELECT
  YEAR(DATE) AS YEAR,
  COUNT(CASE WHEN MONTH(DATE) = 10 THEN 1 END) AS OCTOBER,
  COUNT(CASE WHEN MONTH(DATE) = 11 THEN 1 END) AS NOVEMBER
FROM 替换为你的实际表名
GROUP BY YEAR(DATE)

运行后即可得到你期望的统计结果：按年份分组，分别统计对应月份的日期出现次数。如果需要扩展统计其他月份，新增对应CASE WHEN判断分支即可。

内容的提问来源于stack exchange，提问作者1511