当前位置：极客教程 > SQL > SQL 实例 > SQL 列出一年中所有的星期五

SQL 列出一年中所有的星期五

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'
SQL

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'
SQL

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'
SQL

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

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

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
SQL

现在我们知道了当前年份的第一天，接着使用 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
SQL

最后，调用 DAYNAME 函数筛选出星期五对应的日期。
Oracle
为了找到当前年份所有的星期五，我们必须先列出来当前年份的每一天。首先调用 TRUNC 函数得到当前年份的第一天。

select trunc(sysdate,'y') dy
  from t1
　
DY
-----------
01-JAN-2005
SQL

然后，使用 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
SQL

最后，调用 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
SQL

请记住，根据上述结果集，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
SQL

最后，调用 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
SQL

然后，使用 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
SQL

最后，调用 DAYNAME 函数筛选出星期五对应的日期。
SQL Server
为了找出当前年份所有的星期五，我们必须先列出当前年份的每一天。首先要调用 DATEPART 函数得到当前年份的第一天。从当前日期里减去 DATEPART(DY,GETDATE()) 函数调用的返回值，并加上 1 天，就得到了当前年份的第一天。

select getdate()-datepart(dy,getdate())+1 dy
  from t1
　
DY
-----------
01-JAN-2005
SQL

现在知道了当前年份的第一天，接着使用 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
SQL

最后，调用 DATENAME 函数筛选出星期五对应的日期。对于本解决方案而言，我们必须设置 MAXRECURSION 的值，使之不小于 366（这是为了过滤递归视图 X，使得查询结果都是当前年份的数据，并保证结果集不超过 366 行）。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

SQL 实例

登录

找回密码？

注册