极客教程SQL 列出一年中所有的星期五，对于一周的某一天，你想找出一年中与之对应的所有日期。例如，你希望生成一个列表，列出当前年份所有的星期五。
SQL 列出一年中所有的星期五 问题描述
对于一周的某一天，你想找出一年中与之对应的所有日期。例如，你希望生成一个列表，列出当前年份所有的星期五。
SQL 列出一年中所有的星期五 解决方案
不论使用哪一种数据库，解决本问题的关键都在于先列出当前年份的每一天，然后筛选出符合条件的日期。下面的解决方案以找出所有的星期五为例。
DB2
使用 WITH 递归查询列出当前年份的每一天，然后调用 DAYNAME 函数筛选出星期五对应的日期。
1 with x (dy,yr)
2 as (
3 select dy, year(dy) yr
4 from (
5 select (current_date -
6 dayofyear(current_date) days +1 days) as dy
7 from t1
8 ) tmp1
9 union all
10 select dy+1 days, yr
11 from x
12 where year(dy +1 day) = yr
13 )
14 select dy
15 from x
16 where dayname(dy) = 'Friday'
Oracle
使用 CONNECT BY 递归查询列出当前年份的每一天，然后调用 TO_CHAR 函数筛选出星期五对应的日期。
1 with x
2 as (
3 select trunc(sysdate,'y')+level-1 dy
4 from t1
5 connect by level <=
6 add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
7 )
8 select *
9 from x
10 where to_char( dy, 'dy') = 'fri'
PostgreSQL
使用 GENERATE_SERIES 函数列出当前年份的每一天，然后调用 TO_CHAR 函数筛选出星期五对应的日期。
1 select cast(date_trunc('year',current_date) as date)
2 + x.id as dy
3 from generate_series (
4 0,
5 ( select cast(
6 cast(
7 date_trunc('year',current_date) as date)
8 + interval '1 years' as date)
9 - cast(
10 date_trunc('year',current_date) as date) )-1
11 ) x(id)
12 where to_char(
13 cast(
14 date_trunc('year',current_date)
15 as date)+x.id,'dy') = 'fri'
MySQL
使用数据透视表 T500 列出当前年份的每一天，然后调用 DAYNAME 函数筛选出星期五对应的日期。
1 select dy
2 from (
3 select adddate(x.dy,interval t500.id-1 day) dy
4 from (
5 select dy, year(dy) yr
6 from (
7 select adddate(
8 adddate(current_date,
9 interval -dayofyear(current_date) day),
10 interval 1 day ) dy
11 from t1
12 ) tmp1
13 ) x,
14 t500
15 where year(adddate(x.dy,interval t500.id-1 day)) = x.yr
16 ) tmp2
17 where dayname(dy) = 'Friday'
SQL Server
使用 WITH 递归查询列出当前年份的每一天，然后调用 DAYNAME 函数筛选出星期五对应的日期。
1 with x (dy,yr)
2 as (
3 select dy, year(dy) yr
4 from (
5 select getdate()-datepart(dy,getdate())+1 dy
6 from t1
7 ) tmp1
8 union all
9 select dateadd(dd,1,dy), yr
10 from x
11 where year(dateadd(dd,1,dy)) = yr
12 )
13 select x.dy
14 from x
15 where datename(dw,x.dy) = 'Friday'
16 option (maxrecursion 400)
SQL 列出一年中所有的星期五 扩展知识
DB2
为了找出当前年份所有的星期五，我们必须先列出来当前年份的每一天。第一步要调用 DAYOFYEAR 函数找到当前年份的第一天。从当前日期里减去 DAYOFYEAR(CURRENT_DATE) 函数调用的返回值可以得到上一年 12 月 31 日，再加上 1 天就得到了当前年份的第一天。
select (current_date -
dayofyear(current_date) days +1 days) as dy
from t1
DY
-----------
01-JAN-2005
现在我们知道了当前年份的第一天，接着使用 WITH 子句在当前年份第一天的基础上逐次加上 1 天，直至得到的日期不再属于当前年份。上述得到的结果集将是当前年份的每一天（递归视图 X 查询的部分结果如下所示）。
with x (dy,yr)
as (
select dy, year(dy) yr
from (
select (current_date -
dayofyear(current_date) days +1 days) as dy
from t1
) tmp1
union all
select dy+1 days, yr
from x
where year(dy +1 day) = yr
)
select dy
from x
DY
-----------
01-JAN-2005
...
15-FEB-2005
...
22-NOV-2005
...
31-DEC-2005
最后，调用 DAYNAME 函数筛选出星期五对应的日期。
Oracle
为了找到当前年份所有的星期五，我们必须先列出来当前年份的每一天。首先调用 TRUNC 函数得到当前年份的第一天。
select trunc(sysdate,'y') dy
from t1
DY
-----------
01-JAN-2005
然后，使用 CONNECT BY 子句返回当前年份的每一天（参见第 13 章中的相关内容，以了解如何使用 CONNECT BY 生成行数据）。
顺便说一下，虽然本实例采用了基于
WITH子句的解决方案，但其实也可以使用内嵌视图达到同样目的。
在写作本书时，Oracle 的 WITH 子句并不用于实现递归操作（这不同于 DB2 和 SQL Server），递归操作是由 CONNECT BY 完成的。视图 X 返回的部分结果集如下所示。
with x
as (
select trunc(sysdate,'y')+level-1 dy
from t1
connect by level <=
add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
)
select *
from x
DY
-----------
01-JAN-2005
...
15-FEB-2005
...
22-NOV-2005
...
31-DEC-2005
最后，调用 TO_CHAR 函数筛选出星期五对应的日期。
PostgreSQL
为了找出当前年份所有的星期五，我们必须先把当前年份的每一天都当作一条记录返回。这里需要使用 GENERATE_SERIES 函数。GENERATE_SERIES 函数返回的起点和终点值分别是 0 和当前年份总天数减 1。传递给 GENERATE_SERIES 函数的第一个参数是 0，第二个参数则是一个查询，该查询用于计算出当前年份有多少天。（因为我们要在当前年份第一天的基础上逐日累加，实际上要累加的天数恰好比当前年份的总天数少 1 天，这样才不至于溢出到下一年。）GENERATE_SERIES 函数的第二个参数返回的结果如下所示。
select cast(
cast(
date_trunc('year',current_date) as date)
+ interval '1 years' as date)
- cast(
date_trunc('year',current_date) as date)-1 as cnt
from t1
CNT
---
364
请记住，根据上述结果集，FROM 子句里的 GENERATE_SERIES 函数调用看起来是这样的：GENERATE_SERIES(0,364)。如果是闰年，例如 2004 年，第二个参数则是 365。
为了生成当前年份全部日期的列表，下一步就要把 GENERATE_SERIES 函数的返回值依次加上当前年份的第一天。部分结果如下所示。
select cast(date_trunc('year',current_date) as date)
+ x.id as dy
from generate_series (
0,
( select cast(
cast(
date_trunc('year',current_date) as date)
+ interval '1 years' as date)
- cast(
date_trunc('year',current_date) as date) )-1
) x(id)
DY
----------
01-JAN-2005
...
15-FEB-2005
...
22-NOV-2005
...
31-DEC-2005
最后，调用 TO_CHAR 函数筛选出星期五对应的日期。
MySQL
为了找出当前年份的全部星期五，我们先列出来当前年份的每一天。首先要调用 DAYOFYEAR 函数找出当前年份的第一天。从当前日期里减去 DAYOFYEAR(CURRENT_DATE) 函数调用的返回值，然后再加上 1 天，这样就得到了当前年份的第一天。
select adddate(
adddate(current_date,
interval -dayofyear(current_date) day),
interval 1 day ) dy
from t1
DY
-----------
01-JAN-2005
然后，使用 T500 表生成足够多的行以返回当前年份的每一天。我们要在当前年份第一天的基础上逐一加上 T500.ID 的值，直至当前年份结束。这个操作的部分结果如下。
select adddate(x.dy,interval t500.id-1 day) dy
from (
select dy, year(dy) yr
from (
select adddate(
adddate(current_date,
interval -dayofyear(current_date) day),
interval 1 day ) dy
from t1
) tmp1
) x,
t500
where year(adddate(x.dy,interval t500.id-1 day)) = x.yr
DY
-----------
01-JAN-2005
...
15-FEB-2005
...
22-NOV-2005
...
31-DEC-2005
最后，调用 DAYNAME 函数筛选出星期五对应的日期。
SQL Server
为了找出当前年份所有的星期五，我们必须先列出当前年份的每一天。首先要调用 DATEPART 函数得到当前年份的第一天。从当前日期里减去 DATEPART(DY,GETDATE()) 函数调用的返回值，并加上 1 天，就得到了当前年份的第一天。
select getdate()-datepart(dy,getdate())+1 dy
from t1
DY
-----------
01-JAN-2005
现在知道了当前年份的第一天，接着使用 WITH 子句和 DATEADD 函数在第一天的基础上逐次加上 1 天，直至当前年份的最后一天。这样一来，得到的结果集就是当前年份的每一天（递归视图 X 返回的部分行如下所示）。
with x (dy,yr)
as (
select dy, year(dy) yr
from (
select getdate()-datepart(dy,getdate())+1 dy
from t1
) tmp1
union all
select dateadd(dd,1,dy), yr
from x
where year(dateadd(dd,1,dy)) = yr
)
select x.dy
from x
option (maxrecursion 400)
DY
-----------
01-JAN-2005
...
15-FEB-2005
...
22-NOV-2005
...
31-DEC-2005
最后，调用 DATENAME 函数筛选出星期五对应的日期。对于本解决方案而言，我们必须设置 MAXRECURSION 的值，使之不小于 366（这是为了过滤递归视图 X，使得查询结果都是当前年份的数据，并保证结果集不超过 366 行）。