SQL打印月份日历
需求：随意输入一日期，用 SQL 输出该日期的当月的日历
解题分析：
- 因为日期是随意输入的，很自然的，我们就想到，需要知道输入日期的当月的第一天，以及最后一天，当前月份的天数。
- 因为日历均是按照周来打印的，那么，接下来，获取周数也就必然的步骤。
- 看上去，所有的基础数据都通过以上两步获取了，那么进行行列转换，也就可以得到我们想要的结果集。
依据以上思路，实现的完整SQL脚本如下（PG）：
with recursive cte_base as (
select date_trunc('month', TIMESTAMP '2025-1-03')::date as daydate
, 1 as day
union
select (daydate + 1)::date
, day +1
from cte_base
where day < ((date_trunc('month',TIMESTAMP '2025-1-03') + interval'1 month - 1 day')::date - (date_trunc('month', TIMESTAMP '2025-1-03'))::date + 1)
),
cte_days as (
select extract(isodow from daydate) as dayofweek
, day
, extract(week from daydate) as week
from cte_base
),
cte_tmp as (
select sum(case when dayofweek = 1 then day else null end ) as mon
, sum(case when dayofweek = 2 then day else null end ) as tue
, sum(case when dayofweek = 3 then day else null end ) as wed
, sum(case when dayofweek = 4 then day else null end ) as thu
, sum(case when dayofweek = 5 then day else null end ) as fri
, sum(case when dayofweek = 6 then day else null end ) as sat
, sum(case when dayofweek = 7 then day else null end ) as sun
, week
from cte_days
group by week
)
select week
, mon
, tue
, wed
, thu
, fri
, sat
, sun
from cte_tmp
order by week;
执行一下，输出结果如下图所示：
从输出结果看上去，的确是我们想要的结果，似乎很完美，那么真的完美了吗？
让我们继续改变日期为2022-01-05来试试看，打印结果如下图所示：
发现了什么？
1与2居然排序到了最后，这是为什么呢？再检查一下，不难发现，Week是52，还是上一年的Week，而并非是当年的1，此时依据Week来做排序，必然得不到我们想要的结果。
同理，如果我们尝试日期2019-12-05，打印结果如下图所示：
也依然不是我们想要的结果。
由此，不难想到一个问题：当月份为12月和1月的时候，可能存在week跨年的情况发生，而导致出现如上的问题。
接下来，我们需要做一点点小的技术处理。
技术处理思路分析：
- 既然依据Week进行排序是会导致错误，那么能否构造一个新的排序规则来代替Week排序呢？
- 我们观察一下数据，每一Week的日期都是从小到大排序的，能否借助一下这个日期呢？
- 因为理论上来讲，每一个Week最少会覆盖一天，那么我就获取每一个Week的一天，让它来和Week做个对应关系，就像初中数学几何解题使用到的辅助线一样。这里我们将Week分组，获取最小的一天，当然最大的一天也是可以的。
- 利用每一Week的最小一天，我们再重新排序，获取到的就是正确的排序值了。
- 最后，我们利用这个新的排序规则来对前面的结果集进行排序，最终得到我们想要的结果。至此，此题顺利解决。
依据以上思路，实现的最终完整SQL脚本如下（PG）：
with recursive cte_base as (
select date_trunc('month', TIMESTAMP '2025-01-03')::date as daydate
, 1 as day
union
select (daydate + 1)::date
, day +1
from cte_base
where day < ((date_trunc('month',TIMESTAMP '2025-01-03') + interval'1 month - 1 day')::date - (date_trunc('month', TIMESTAMP '2025-01-03'))::date + 1)
),
cte_days as (
select extract(isodow from daydate) as dayofweek
, day
, extract(week from daydate) as week
from cte_base
),
cte_tmp as (
select sum(case when dayofweek = 1 then day else null end ) as mon
, sum(case when dayofweek = 2 then day else null end ) as tue
, sum(case when dayofweek = 3 then day else null end ) as wed
, sum(case when dayofweek = 4 then day else null end ) as thu
, sum(case when dayofweek = 5 then day else null end ) as fri
, sum(case when dayofweek = 6 then day else null end ) as sat
, sum(case when dayofweek = 7 then day else null end ) as sun
, week
from cte_days
group by week
order by week
),
cte_min as (
select min(day) as minday
, week
from cte_days
group by week
),
cte_rownumber as (
select row_number() over (order by minday) as rownumber
, week
from cte_min
)
select t.week
, t.mon
, t.tue
, t.wed
, t.thu
, t.fri
, t.sat
, t.sun
from cte_rownumber r
join cte_tmp t
on r.week = t.week
order by r.rownumber;
如果有不妥之处，请大家批评指正。
谢谢