database - IBM DB2 PIVOT 一个完整的日期表
问题描述
我在 DB2 中有这张表:
DATE
----------
09/11/2021
06/10/2021
28/11/2021
17/11/2021
11/10/2021
24/11/2021
07/11/2021
30/11/2021
我想计算一个日期在表格中出现的次数,并按年和月分组,并显示如下:
| YEAR | OCTOBER | NOVEMBER |
----------------------------
| 2021 | 2 | 6 |
解决方案
由于月份是一个已知数量,您可以使用案例语句的总和:
select year(datecol) as year
,sum(case when month(datecol) = 1 then 1 else 0 end) as jan
,sum(case when month(datecol) = 2 then 1 else 0 end) as feb
,sum(case when month(datecol) = 3 then 1 else 0 end) as mar
,sum(case when month(datecol) = 4 then 1 else 0 end) as apr
,sum(case when month(datecol) = 5 then 1 else 0 end) as may
,sum(case when month(datecol) = 6 then 1 else 0 end) as jun
,sum(case when month(datecol) = 7 then 1 else 0 end) as jul
,sum(case when month(datecol) = 8 then 1 else 0 end) as aug
,sum(case when month(datecol) = 9 then 1 else 0 end) as sep
,sum(case when month(datecol) = 10 then 1 else 0 end) as oct
,sum(case when month(datecol) = 11 then 1 else 0 end) as nov
,sum(case when month(datecol) = 12 then 1 else 0 end) as dec
from datetest
group by year(datecol)
order by 1;
这将为您提供与此类似的输出:
YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2018 0 0 0 0 0 0 0 0 0 0 3 0
2019 0 0 0 0 0 0 0 0 0 1 2 0
2020 0 0 0 0 0 0 0 0 0 1 1 0
2021 0 0 0 0 0 0 0 0 0 2 6 0
推荐阅读
- python - 如何将文件从数据框中移动到单独的文件夹中?
- c# - ASP.NET core 5 MVC Null 下拉列表选项
- java - 如何在另一个应用程序中共享来自内部存储的媒体?
- django - 命名 Django 模型“模型”是否有任何问题?
- xcode - 如何将底部导航和侧边菜单放在同一个应用程序中
- python - Python For 循环作业
- web - 资源不会显示在网络监视器(firefox)中
- sql - BigQuery:最好地使用 UNNEST 数组
- php - 502 Bad Gateway - Google Cloud 在冷启动时使用 php-fpm(未找到套接字)运行
- java - 更改 JVM 选项后如何诊断无法启动 jenkins?