极客教程SQL 生成日历，你想为当前月份生成一个日历。日历的格式应该像我们桌面上摆放的台历那样横向有 7 列，（通常）纵向有 5 行。
SQL 生成日历 问题描述
你想为当前月份生成一个日历。日历的格式应该像我们桌面上摆放的台历那样横向有 7 列，（通常）纵向有 5 行。
SQL 生成日历 解决方案
下述每种解决方案都略有不同之处，但它们解决问题的思路是相同的。列出当前月份的每一天，然后根据每一天是星期几确定输出顺序以生成日历。
日历有许多种不同的格式。例如，Unix 下的 cal 命令输出的格式是从星期日到星期六。本实例基于 ISO 标准，按照星期一到星期五的顺序生成日历。掌握了本实例提供的解决方案之后，就能根据自己的喜好轻松地重新安排日历的格式了。
如果我们试图使用 SQL 完成各种格式化操作以便让输出结果更具可读性的话，那么查询语句也会变得更长。不要被这些长长的查询吓住；如果把本实例里的各种查询分解开，并一段一段地执行的话，我们就会发现它们实际上已经足够简洁了。
DB2
使用 WITH 递归查询列出当前月份的每一天，然后使用 CASE 表达式和 MAX 函数根据每一天是星期几编排输出顺序。
1 with x(dy,dm,mth,dw,wk)
2 as (
3 select (current_date -day(current_date) day +1 day) dy,
4 day((current_date -day(current_date) day +1 day)) dm,
5 month(current_date) mth,
6 dayofweek(current_date -day(current_date) day +1 day) dw,
7 week_iso(current_date -day(current_date) day +1 day) wk
8 from t1
9 union all
10 select dy+1 day, day(dy+1 day), mth,
11 dayofweek(dy+1 day), week_iso(dy+1 day)
12 from x
13 where month(dy+1 day) = mth
14 )
15 select max(case dw when 2 then dm end) as Mo,
16 max(case dw when 3 then dm end) as Tu,
17 max(case dw when 4 then dm end) as We,
18 max(case dw when 5 then dm end) as Th,
19 max(case dw when 6 then dm end) as Fr,
20 max(case dw when 7 then dm end) as Sa,
21 max(case dw when 1 then dm end) as Su
22 from x
23 group by wk
24 order by wk
Oracle
使用 CONNECT BY 递归查询列出当前月份的每一天，然后使用 CASE 表达式和 MAX 函数根据每一天是星期几编排输出顺序。
1 with x
2 as (
3 select *
4 from (
5 select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
6 to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
7 to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
8 to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
9 to_char(sysdate,'mm') mth
10 from dual
11 connect by level <= 31
12 )
13 where curr_mth = mth
14 )
15 select max(case dw when 2 then dm end) Mo,
16 max(case dw when 3 then dm end) Tu,
17 max(case dw when 4 then dm end) We,
18 max(case dw when 5 then dm end) Th,
19 max(case dw when 6 then dm end) Fr,
20 max(case dw when 7 then dm end) Sa,
21 max(case dw when 1 then dm end) Su
22 from x
23 group by wk
24 order by wk
PostgreSQL
使用 GENERATE_SERIES 函数列出当前月份的每一天。然后使用 CASE 表达式和 MAX 函数根据每一天是星期几编排输出顺序。
1 select max(case dw when 2 then dm end) as Mo,
2 max(case dw when 3 then dm end) as Tu,
3 max(case dw when 4 then dm end) as We,
4 max(case dw when 5 then dm end) as Th,
5 max(case dw when 6 then dm end) as Fr,
6 max(case dw when 7 then dm end) as Sa,
7 max(case dw when 1 then dm end) as Su
8 from (
9 select *
10 from (
11 select cast(date_trunc('month',current_date) as date)+x.id,
12 to_char(
13 cast(
14 date_trunc('month',current_date)
15 as date)+x.id,'iw') as wk,
16 to_char(
17 cast(
18 date_trunc('month',current_date)
19 as date)+x.id,'dd') as dm,
20 cast(
21 to_char(
22 cast(
23 date_trunc('month',current_date)
24 as date)+x.id,'d') as integer) as dw,
25 to_char(
26 cast(
27 date_trunc('month',current_date)
28 as date)+x.id,'mm') as curr_mth,
29 to_char(current_date,'mm') as mth
30 from generate_series (0,31) x(id)
31 ) x
32 where mth = curr_mth
33 ) y
34 group by wk
35 order by wk
MySQL
使用 T500 列出当前月份的每一天，然后使用 CASE 表达式和 MAX 函数根据每一天是星期几编排输出顺序。
1 select max(case dw when 2 then dm end) as Mo,
2 max(case dw when 3 then dm end) as Tu,
3 max(case dw when 4 then dm end) as We,
4 max(case dw when 5 then dm end) as Th,
5 max(case dw when 6 then dm end) as Fr,
6 max(case dw when 7 then dm end) as Sa,
7 max(case dw when 1 then dm end) as Su
8 from (
9 select date_format(dy,'%u') wk,
10 date_format(dy,'%d') dm,
11 date_format(dy,'%w')+1 dw
12 from (
13 select adddate(x.dy,t500.id-1) dy,
14 x.mth
15 from (
16 select adddate(current_date,-dayofmonth(current_date)+1) dy,
17 date_format(
18 adddate(current_date,
19 -dayofmonth(current_date)+1),
20 '%m') mth
21 from t1
22 ) x,
23 t500
24 where t500.id <= 31
25 and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
26 ) y
27 ) z
28 group by wk
29 order by wk
SQL Server
使用 WITH 递归查询列出当前月份的每一天，然后使用 CASE 表达式和 MAX 函数根据每一天是星期几编排输出顺序。
1 with x(dy,dm,mth,dw,wk)
2 as (
3 select dy,
4 day(dy) dm,
5 datepart(m,dy) mth,
6 datepart(dw,dy) dw,
7 case when datepart(dw,dy) = 1
8 then datepart(ww,dy)-1
9 else datepart(ww,dy)
10 end wk
11 from (
12 select dateadd(day,-day(getdate())+1,getdate()) dy
13 from t1
14 ) x
15 union all
16 select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth,
17 datepart(dw,dateadd(d,1,dy)),
18 case when datepart(dw,dateadd(d,1,dy)) = 1
19 then datepart(wk,dateadd(d,1,dy))-1
20 else datepart(wk,dateadd(d,1,dy))
21 end
22 from x
23 where datepart(m,dateadd(d,1,dy)) = mth
24 )
25 select max(case dw when 2 then dm end) as Mo,
26 max(case dw when 3 then dm end) as Tu,
27 max(case dw when 4 then dm end) as We,
28 max(case dw when 5 then dm end) as Th,
29 max(case dw when 6 then dm end) as Fr,
30 max(case dw when 7 then dm end) as Sa,
31 max(case dw when 1 then dm end) as Su
32 from x
33 group by wk
34 order by wk
SQL 生成日历 讨论
DB2
首先要列出当前月份的每一天。这要用到 WITH 递归查询（如果你使用的 DB2 版本不支持 WITH，则不妨借助类似 T500 这样的数据透视表，具体参考 MySQL 的解决方案）。除了当前月份的每一天（别名 DM），我们还要提取出该日期的不同组成部分：它是星期几（别名 DW），当前的月份（别名 MTH），以及符合 ISO 标准的周序号（别名 WK）。实际的递归操作发生之前（UNION ALL 之前的部分）的递归视图 X 的查询结果如下所示。
select (current_date -day(current_date) day +1 day) dy,
day((current_date -day(current_date) day +1 day)) dm,
month(current_date) mth,
dayofweek(current_date -day(current_date) day +1 day) dw,
week_iso(current_date -day(current_date) day +1 day) wk
from t1
DY DM MTH DW WK
----------- -- --- ---------- --
01-JUN-2005 01 06 4 22
接下来就要不断地递增 DM 值（在当前月份里向前推进），直至到达月末。因为我们会遍历当前月份中的每一天，也就能逐一获得每一天是星期几，以及相应的符合 ISO 标准的周序号。部分查询结果显示如下所示。
with x(dy,dm,mth,dw,wk)
as (
select (current_date -day(current_date) day +1 day) dy,
day((current_date -day(current_date) day +1 day)) dm,
month(current_date) mth,
dayofweek(current_date -day(current_date) day +1 day) dw,
week_iso(current_date -day(current_date) day +1 day) wk
from t1
union all
select dy+1 day, day(dy+1 day), mth,
dayofweek(dy+1 day), week_iso(dy+1 day)
from x
where month(dy+1 day) = mth
)
select *
from x
DY DM MTH DW WK
----------- -- --- ---------- --
01-JUN-2005 01 06 4 22
02-JUN-2005 02 06 5 22
...
21-JUN-2005 21 06 3 25
22-JUN-2005 22 06 4 25
...
30-JUN-2005 30 06 5 26
到目前为止，得到的结果包括：当前月份的每一天，两位数字表示的日期，两位数字表示的月份，一位数字表示的星期几（1 ～ 7 分别代表从星期日到星期六的每一天），以及两位数字表示的、符合 ISO 标准的周序号。有了这些信息，我们就能使用 CASE 表达式来决定每一个 DM 值（当前月份的每一天）会落到一周的哪一天。部分查询结果如下所示。
with x(dy,dm,mth,dw,wk)
as (
select (current_date -day(current_date) day +1 day) dy,
day((current_date -day(current_date) day +1 day)) dm,
month(current_date) mth,
dayofweek(current_date -day(current_date) day +1 day) dw,
week_iso(current_date -day(current_date) day +1 day) wk
from t1
union all
select dy+1 day, day(dy+1 day), mth,
dayofweek(dy+1 day), week_iso(dy+1 day)
from x
where month(dy+1 day) = mth
)
select wk,
case dw when 2 then dm end as Mo,
case dw when 3 then dm end as Tu,
case dw when 4 then dm end as We,
case dw when 5 then dm end as Th,
case dw when 6 then dm end as Fr,
case dw when 7 then dm end as Sa,
case dw when 1 then dm end as Su
from x
WK MO TU WE TH FR SA SU
-- -- -- -- -- -- -- --
22 01
22 02
22 03
22 04
22 05
23 06
23 07
23 08
23 09
23 10
23 11
23 12
在上面的部分输出结果里，可以看到一周里的每一天都作为单独的一行被返回。现在需要以周为单位为数据分组，并把同一周的七天合并为一行。调用聚合函数 MAX，并按照 WK（符合 ISO 标准的周序号）分组，这样就能把一周七天合并到一行里。为了合理地安排输出格式并保证按日期顺序输出，还要根据 WK 对结果做排序。最终结果如下所示。
with x(dy,dm,mth,dw,wk)
as (
select (current_date -day(current_date) day +1 day) dy,
day((current_date -day(current_date) day +1 day)) dm,
month(current_date) mth,
dayofweek(current_date -day(current_date) day +1 day) dw,
week_iso(current_date -day(current_date) day +1 day) wk
from t1
union all
select dy+1 day, day(dy+1 day), mth,
dayofweek(dy+1 day), week_iso(dy+1 day)
from x
where month(dy+1 day) = mth
)
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from x
group by wk
order by wk
MO TU WE TH FR SA SU
-- -- -- -- -- -- --
01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
Oracle
首先使用 CONNECT BY 递归查询生成当前月份的每一天。如果手边没有 Oracle 9i 或者更高版本的数据库，我们就无法按这种方式使用 CONNECT BY。如此一来，则需要借助 MySQL 解决方案里出现过的 T500 这样的数据透视表。
除了当前月份的每一天之外，我们还需要其他信息：当前月份每一天的日期部分（别名 DM），每一天分别是星期几（别名 DW），当前的月份（别名 MTH），以及符合 ISO 标准的周序号（别名 WK）。WITH 视图 X 里与当前月份第一天相关的查询结果如下所示。
select trunc(sysdate,'mm') dy,
to_char(trunc(sysdate,'mm'),'dd') dm,
to_char(sysdate,'mm') mth,
to_number(to_char(trunc(sysdate,'mm'),'d')) dw,
to_char(trunc(sysdate,'mm'),'iw') wk
from dual
DY DM MT DW WK
----------- -- -- ---------- --
01-JUN-2005 01 06 4 22
接下来就要不断地递增 DM 值（在当前月份里向前推进），直至到达月末。因为我们会遍历当前月份的每一天，也就能逐一获得每一天是星期几，以及相应的符合 ISO 标准的周序号。部分结果如下所示（为了增加可读性，额外添加了每一天的日期）。
with x
as (
select *
from (
select trunc(sysdate,'mm')+level-1 dy,
to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
to_char(sysdate,'mm') mth
from dual
connect by level <= 31
)
where curr_mth = mth
)
select *
from x
DY WK DM DW CU MT
----------- -- -- ---------- -- --
01-JUN-2005 22 01 4 06 06
02-JUN-2005 22 02 5 06 06
...
21-JUN-2005 25 21 3 06 06
22-JUN-2005 25 22 4 06 06
...
30-JUN-2005 26 30 5 06 06
到目前为止，当前月份的每一天都作为单独的一行被返回。每一行包括：两位数字表示的日期，两位数字表示的月份，一位数字表示的星期几（1 ～ 7 分别代表从星期日到星期六的每一天），以及两位数字表示的、符合 ISO 标准的周序号。有了这些信息，我们就能使用 CASE 表达式来决定每一个 DM 值（当前月份的每一天）会落到一周的哪一天。部分查询结果如下所示。
with x
as (
select *
from (
select trunc(sysdate,'mm')+level-1 dy,
to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
to_char(sysdate,'mm') mth
from dual
connect by level <= 31
)
where curr_mth = mth
)
select wk,
case dw when 2 then dm end as Mo,
case dw when 3 then dm end as Tu,
case dw when 4 then dm end as We,
case dw when 5 then dm end as Th,
case dw when 6 then dm end as Fr,
case dw when 7 then dm end as Sa,
case dw when 1 then dm end as Su
from x
WK MO TU WE TH FR SA SU
-- -- -- -- -- -- -- --
22 01
22 02
22 03
22 04
22 05
23 06
23 07
23 08
23 09
23 10
23 11
23 12
在上面的部分输出结果里，可以看到一周里的每一天都作为单独的一行被返回，而日期则被放置于 7 列中与 DW 值相对应的那一列。我们需要把一周七天都归并到一行里去。调用聚合函数 MAX，并按照 WK（符合 ISO 标准的周序号）分组，这样就能把一周七天合并到一行了。为了保证按日期顺序输出，还要根据 WK 对结果排序，最终结果如下所示。
with x
as (
select *
from (
select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
to_char(sysdate,'mm') mth
from dual
connect by level <= 31
)
where curr_mth = mth
)
select max(case dw when 2 then dm end) Mo,
max(case dw when 3 then dm end) Tu,
max(case dw when 4 then dm end) We,
max(case dw when 5 then dm end) Th,
max(case dw when 6 then dm end) Fr,
max(case dw when 7 then dm end) Sa,
max(case dw when 1 then dm end) Su
from x
group by wk
order by wk
MO TU WE TH FR SA SU
-- -- -- -- -- -- --
01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
PostgreSQL
使用 GENERATE_SERIES 函数把当前月份的每一天都当作单独的一行返回。如果你使用的 PostgreSQL 版本不支持 GENERATE_SERIES，不妨参考 MySQL 解决方案，改为借助一个数据透视表来实现同样的功能。
对于当前月份的每一天，分别提取出下列信息：当前月份每一天的日期部分（别名 DM），每一天分别是星期几（别名 DW），当前的月份（别名 MTH），以及符合 ISO 标准的周序号（别名 WK）。尽管格式化和显式类型转换相关的代码大大降低了本解决方案的可读性，但整个查询其实并不复杂。内嵌视图 X 的部分查询结果如下所示。
select cast(date_trunc('month',current_date) as date)+x.id as dy,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'iw') as wk,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'dd') as dm,
cast(
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'d') as integer) as dw,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'mm') as curr_mth,
to_char(current_date,'mm') as mth
from generate_series (0,31) x(id)
DY WK DM DW CU MT
----------- -- -- ---------- -- --
01-JUN-2005 22 01 4 06 06
02-JUN-2005 22 02 5 06 06
...
21-JUN-2005 25 21 3 06 06
22-JUN-2005 25 22 4 06 06
...
30-JUN-2005 26 30 5 06 06
注意，当遍历当前月份的每一天时，我们同时能知道每一天是星期几，以及符合 ISO 标准的周序号。为了保证遍历的范围不超出当前月份，我们按照条件 CURR_MTH = MTH 对返回的日期做了过滤（每个日期所对应的月份应该是当前月份）。到目前为止，得到的结果包括：两位数字表示的日期，两位数字表示的月份，一位数字表示的星期几（1 ～ 7 分别代表从星期日到星期六的每一天），以及两位数字表示的、符合 ISO 标准的周序号。下一步需要使用 CASE 表达式来决定每一个 DM 值（当前月份的每一天）会落到一周中的哪一天。部分查询结果如下所示。
select case dw when 2 then dm end as Mo,
case dw when 3 then dm end as Tu,
case dw when 4 then dm end as We,
case dw when 5 then dm end as Th,
case dw when 6 then dm end as Fr,
case dw when 7 then dm end as Sa,
case dw when 1 then dm end as Su
from (
select *
from (
select cast(date_trunc('month',current_date) as date)+x.id,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'iw') as wk,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'dd') as dm,
cast(
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'d') as integer) as dw,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'mm') as curr_mth,
to_char(current_date,'mm') as mth
from generate_series (0,31) x(id)
) x
where mth = curr_mth
) y
WK MO TU WE TH FR SA SU
-- -- -- -- -- -- -- --
22 01
22 02
22 03
22 04
22 05
23 06
23 07
23 08
23 09
23 10
23 11
23 12
在上面的部分输出结果里，可以看到一周里的每一天都被作为单独的一行被返回，而日期则被放置于 7 列中与 DW 值相对应的那一列。我们需要把一周七天都合并到一行中。因此接下来要调用聚合函数 MAX，并按照 WK（符合 ISO 标准的周序号）分组。这样一来，我们就能把一周七天合并到一行里去了，就像在真实的日历上看到的那样。为了保证按日期顺序输出，还要根据 WK 对结果做排序。最终结果如下所示。
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from (
select *
from (
select cast(date_trunc('month',current_date) as date)+x.id,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'iw') as wk,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'dd') as dm,
cast(
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'d') as integer) as dw,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'mm') as curr_mth,
to_char(current_date,'mm') as mth
from generate_series (0,31) x(id)
) x
where mth = curr_mth
) y
group by wk
order by wk
MO TU WE TH FR SA SU
-- -- -- -- -- -- --
01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
MySQL
首先为当前月份的每一天生成单独的一行。为了实现此目的，需要使用 T500 表。在当前月份第一天的基础上依次加上 T500 表的每一个值，就能得到当前月份的每一天了。
对于每一个日期，需要提取出如下的信息：当前月份每一天的日期部分（别名 DM），每一天分别是星期几（别名 DW），当前的月份（别名 MTH），以及符合 ISO 标准的周序号（别名 WK）。内嵌视图 X 返回当前月份的第一天，以及两位数字表示的当前月份。结果如下所示。
select adddate(current_date,-dayofmonth(current_date)+1) dy,
date_format(
adddate(current_date,
-dayofmonth(current_date)+1),
'%m') mth
from t1
DY MT
----------- --
01-JUN-2005 06
下一步要遍历当前月份，从第一天开始，依次返回当前月份的每一天。注意，我们会遍历当前月份的每一天，并返回每个日期是星期几以及符合 ISO 标准的周序号。为了保证遍历操作不超出范围，只筛选出那些属于当前月份的日期（每一天对应的月份应该等于当前日期所属的月份）。内嵌视图 Y 的部分查询结果如下所示。
select date_format(dy,'%u') wk,
date_format(dy,'%d') dm,
date_format(dy,'%w')+1 dw
from (
select adddate(x.dy,t500.id-1) dy,
x.mth
from (
select adddate(current_date,-dayofmonth(current_date)+1) dy,
date_format(
adddate(current_date,
-dayofmonth(current_date)+1),
'%m') mth
from t1
) x,
t500
where t500.id <= 31
and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
) y
WK DM DW
-- -- ----------
22 01 4
22 02 5
...
25 21 3
25 22 4
...
26 30 5
对于当前月份的每一天，现在知道了下列信息：两位数字表示的日期部分（DM），一位数字表示的星期几（DW），以及两位数字表示的、符合 ISO 标准的周序号（WK）。有了这些信息之后，我们就能借助 CASE 表达式来决定每一个 DM 值（当前月份的每一天）会落到一周的哪一天。部分查询结果如下所示。
select case dw when 2 then dm end as Mo,
case dw when 3 then dm end as Tu,
case dw when 4 then dm end as We,
case dw when 5 then dm end as Th,
case dw when 6 then dm end as Fr,
case dw when 7 then dm end as Sa,
case dw when 1 then dm end as Su
from (
select date_format(dy,'%u') wk,
date_format(dy,'%d') dm,
date_format(dy,'%w')+1 dw
from (
select adddate(x.dy,t500.id-1) dy,
x.mth
from (
select adddate(current_date,-dayofmonth(current_date)+1) dy,
date_format(
adddate(current_date,
-dayofmonth(current_date)+1),
'%m') mth
from t1
) x,
t500
where t500.id <= 31
and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
) y
) z
WK MO TU WE TH FR SA SU
-- -- -- -- -- -- -- --
22 01
22 02
22 03
22 04
22 05
23 06
23 07
23 08
23 09
23 10
23 11
23 12
在上面的部分输出结果里，可以看到一周里的每一天都作为单独的一行被返回。每一行里，日期值都被放置于与 DW 值相对应的那一列。现在我们需要把一周七天都归并到一行里去。因此要调用聚合函数 MAX，并按照 WK（符合 ISO 标准的周序号）分组。为保证按日期顺序输出，还要根据 WK 对结果排序。最终结果如下所示。
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from (
select date_format(dy,'%u') wk,
date_format(dy,'%d') dm,
date_format(dy,'%w')+1 dw
from (
select adddate(x.dy,t500.id-1) dy,
x.mth
from (
select adddate(current_date,-dayofmonth(current_date)+1) dy,
date_format(
adddate(current_date,
-dayofmonth(current_date)+1),
'%m') mth
from t1
) x,
t500
where t500.id <= 31
and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
) y
) z
group by wk
order by wk
MO TU WE TH FR SA SU
-- -- -- -- -- -- --
01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
SQL Server
首先把当前月份的每一天当作单独的一行返回，可以使用 WITH 递归查询来做到这一点。如果你使用的 SQL Server 版本不支持 WITH 递归查询，可以参考 MySQL 的解决方案，借助数据透视表达到同样的目的。对于每一行返回的值，需要获取如下信息：当前月份每一天的日期部分（别名 DM），每一天分别是星期几（别名 DW），当前的月份（别名 MTH），以及符合 ISO 标准的周序号（别名 WK）。实际的递归操作发生之前（UNION ALL 之前的部分）的递归视图 X 的查询结果如下所示。
select dy,
day(dy) dm,
datepart(m,dy) mth,
datepart(dw,dy) dw,
case when datepart(dw,dy) = 1
then datepart(ww,dy)-1
else datepart(ww,dy)
end wk
from (
select dateadd(day,-day(getdate())+1,getdate()) dy
from t1
)x
DY DM MTH DM WK
----------- -- --- ---------- --
01-JUN-2005 1 6 4 23
接下来就要不断地递增 DM 值（在当前月份里向前推进），直至到达月末。因为我们会遍历当前月份的每一天，也就能逐一获取到每一天是星期几，以及相应的符合 ISO 标准的周序号。部分查询结果如下所示。
with x(dy,dm,mth,dw,wk)
as (
select dy,
day(dy) dm,
datepart(m,dy) mth,
datepart(dw,dy) dw,
case when datepart(dw,dy) = 1
then datepart(ww,dy)-1
else datepart(ww,dy)
end wk
from (
select dateadd(day,-day(getdate())+1,getdate()) dy
from t1
) x
union all
select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth,
datepart(dw,dateadd(d,1,dy)),
case when datepart(dw,dateadd(d,1,dy)) = 1
then datepart(wk,dateadd(d,1,dy))-1
else datepart(wk,dateadd(d,1,dy))
end
from x
where datepart(m,dateadd(d,1,dy)) = mth
)
select *
from x
DY DM MTH DW WK
----------- -- --- ---------- --
01-JUN-2005 01 06 4 23
02-JUN-2005 02 06 5 23
...
21-JUN-2005 21 06 3 26
22-JUN-2005 22 06 4 26
...
30-JUN-2005 30 06 5 27
对于当前月份的每一天，现在得到的结果包括：两位数字表示的日期，两位数字表示的月份，一位数字表示的星期几（1 ～ 7 分别代表从星期日到星期六的每一天），以及两位数字表示的、符合 ISO 标准的周序号。
现在，我们就能使用 CASE 表达式来决定每一个 DM 值（当前月份的每一天）会落到一周的哪一天。部分查询结果如下所示。
with x(dy,dm,mth,dw,wk)
as (
select dy,
day(dy) dm,
datepart(m,dy) mth,
datepart(dw,dy) dw,
case when datepart(dw,dy) = 1
then datepart(ww,dy)-1
else datepart(ww,dy)
end wk
from (
select dateadd(day,-day(getdate())+1,getdate()) dy
from t1
) x
union all
select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth,
datepart(dw,dateadd(d,1,dy)),
case when datepart(dw,dateadd(d,1,dy)) = 1
then datepart(wk,dateadd(d,1,dy))-1
else datepart(wk,dateadd(d,1,dy))
end
from x
where datepart(m,dateadd(d,1,dy)) = mth
)
select case dw when 2 then dm end as Mo,
case dw when 3 then dm end as Tu,
case dw when 4 then dm end as We,
case dw when 5 then dm end as Th,
case dw when 6 then dm end as Fr,
case dw when 7 then dm end as Sa,
case dw when 1 then dm end as Su
from x
WK MO TU WE TH FR SA SU
-- -- -- -- -- -- -- --
22 01
22 02
22 03
22 04
22 05
23 06
23 07
23 08
23 09
23 10
23 11
23 12
每一天都作为单独的一行被返回。在每一行里，日期值被放置在与 DW 值相对应的那一列。因此，我们需要把一周七天都归并到一行里去。为达到此目的，针对行数据按照 WK（符合 ISO 标准的周序号）分组，并针对不同的列执行 MAX 函数。查询结果将会以日历的形式输出，如下所示。
with x(dy,dm,mth,dw,wk)
as (
select dy,
day(dy) dm,
datepart(m,dy) mth,
datepart(dw,dy) dw,
case when datepart(dw,dy) = 1
then datepart(ww,dy)-1
else datepart(ww,dy)
end wk
from (
select dateadd(day,-day(getdate())+1,getdate()) dy
from t1
) x
union all
select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth,
datepart(dw,dateadd(d,1,dy)),
case when datepart(dw,dateadd(d,1,dy)) = 1
then datepart(wk,dateadd(d,1,dy))-1
else datepart(wk,dateadd(d,1,dy))
end
from x
where datepart(m,dateadd(d,1,dy)) = mth
)
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from x
group by wk
order by wk
MO TU WE TH FR SA SU
-- -- -- -- -- -- --
01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30