SQL 统计一年中有多少个星期一|极客教程

SQL 统计一年中有多少个星期一，你想知道一年中有多少个星期一、星期二、星期三等。

SQL 统计一年中有多少个星期一问题描述

你想知道一年中有多少个星期一、星期二、星期三等。

SQL 统计一年中有多少个星期一解决方案

为了统计出一年里每个“星期 x”出现的次数，我们必须：
(1) 生成一年里所有可能的日期值；
(2) 格式化上述日期值，并找出它们分别是星期几；
(3) 统计每个“星期 x”出现的次数。
DB2
使用 WITH 递归查询，这样就不需要对一个至少含有 366 行记录的表做 SELECT 查询了。使用 DAYNAME 函数获知每一个日期是星期几，然后统计每个“星期 x”出现的次数。

 1  with x (start_date,end_date)
 2  as (
 3  select start_date,
 4         start_date + 1 year end_date
 5    from (
 6  select (current_date -
 7          dayofyear(current_date) day)
 8          +1 day as start_date
 9    from t1
10         )tmp
11   union all
12  select start_date + 1 day, end_date
13    from x
14   where start_date + 1 day < end_date
15  )
16  select dayname(start_date),count(*)
17    from x
18   group by dayname(start_date)

MySQL
针对 T500 表做 SELECT 查询以产生出足够行数的结果集，每一行记录代表一年中的一个日期。使用 DATE_FORMAT 函数获知每一个日期是星期几，然后统计每个“星期 x”出现的次数。

 1  select date_format(
 2            date_add(
 3                cast(
 4              concat(year(current_date),'-01-01')
 5                     as date),
 6                     interval t500.id-1 day),
 7                     '%W') day,
 8         count(*)
 9    from t500
10   where t500.id <= datediff(
11                        cast(
12                      concat(year(current_date)+1,'-01-01')
13                             as date),
14                        cast(
15                      concat(year(current_date),'-01-01')
16                             as date))
17   group by date_format(
18               date_add(
19                   cast(
20                 concat(year(current_date),'-01-01')
21                        as date),
22                        interval t500.id-1 day),
23                        '%W')

Oracle
对于 Oracle 9i 及其后续版本，可以使用 CONNECT BY 递归查询返回一年中的每一天。如果是 Oracle 8i 或更早版本，则需要针对 T500 表做 SELECT 查询以产生出足够行数的结果集，每一行记录代表一年中的一个日期。不论哪种方法，都需要使用 TO_CHAR 函数以获知每一个日期是星期几，然后统计每个“星期 x”出现的次数。
首先来看一下 CONNECT BY 解决方案。

 1 with x as (
 2 select level lvl
 3   from dual
 4  connect by level <= (
 5    add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 6  )
 7 )
 8 select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)
 9   from x
10  group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')

其次是针对 Oracle 早期版本的解决方案。

1 select to_char(trunc(sysdate,'y')+rownum-1,'DAY'),
2        count(*)
3   from t500
4  where rownum <= (add_months(trunc(sysdate,'y'),12)
5                   - trunc(sysdate,'y'))
6  group by to_char(trunc(sysdate,'y')+rownum-1,'DAY')

PostgreSQL
使用内置函数 GENERATE_SERIES 为一年中的每一天生成一行记录。然后使用 TO_CHAR 函数获知每一个日期是星期几。最后，统计每个“星期 x”出现的次数。例如：

 1  select to_char(
 2            cast(
 3      date_trunc('year',current_date)
 4                 as date) + gs.id-1,'DAY'),
 5         count(*)
 6    from generate_series(1,366) gs(id)
 7   where gs.id <= (cast
 8                    ( date_trunc('year',current_date) +
 9                         interval '12 month' as date) -
10  cast(date_trunc('year',current_date)
11                        as date))
12   group by to_char(
13               cast(
14         date_trunc('year',current_date)
15            as date) + gs.id-1,'DAY')

SQL Server
使用 WITH 递归查询，这样就不需要对一个至少含有 366 行记录的表做 SELECT 查询了。如果是早期不支持 WITH 子句的 SQL Server 版本，参考 Oracle 解决方案里使用数据透视表的做法。使用 DATENAME 函数获知一个日期是星期几，然后统计每个“星期 x”出现的次数。例如：

 1  with x (start_date,end_date)
 2  as (
 3  select start_date,
 4         dateadd(year,1,start_date) end_date
 5    from (
 6  select cast(
 7         cast(year(getdate()) as varchar) + '-01-01'
 8              as datetime) start_date
 9    from t1
10         ) tmp
11  union all
12  select dateadd(day,1,start_date), end_date
13    from x
14   where dateadd(day,1,start_date) < end_date
15  )
16  select datename(dw,start_date),count(*)
17    from x
18   group by datename(dw,start_date)
19 OPTION (MAXRECURSION 366)

SQL 统计一年中有多少个星期一扩展知识

DB2
WITH 递归查询视图 X 里的内嵌视图 TMP 返回当前年份第一天的日期，如下所示。

select (current_date -
        dayofyear(current_date) day)
        +1 day as start_date
  from t1
　
START_DATE
-----------
01-JAN-2005

然后在 START_DATE 基础上加上一年，这样我们就知道了这一年的第一天和最后一天的日期。我们需要知道这两个日期，因为要生成这一年的所有日期。START_DATE 和 END_DATE 如下所示。

select start_date,
       start_date + 1 year end_date
  from (
select (current_date -
        dayofyear(current_date) day)
        +1 day as start_date
  from t1
       ) tmp
　
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006

下一步是为 START_DATE 加上一天，如此循环往复，直到它等于 END_DATE。下面展示了 WITH 递归查询视图 X 返回的结果集的一部分。

with x (start_date,end_date)
as (
select start_date,
       start_date + 1 year end_date
  from (
select (current_date -
        dayofyear(current_date) day)
        +1 day as start_date
  from t1
       ) tmp
 union all
select start_date + 1 day, end_date
  from x
 where start_date + 1 day < end_date
)
select * from x
　
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006
02-JAN-2005 01-JAN-2006
03-JAN-2005 01-JAN-2006
...
29-JAN-2005 01-JAN-2006
30-JAN-2005 01-JAN-2006
31-JAN-2005 01-JAN-2006
...
01-DEC-2005 01-JAN-2006
02-DEC-2005 01-JAN-2006
03-DEC-2005 01-JAN-2006
...
29-DEC-2005 01-JAN-2006
30-DEC-2005 01-JAN-2006
31-DEC-2005 01-JAN-2006

最后，针对 WITH 递归查询视图 X 的返回结果调用 DAYNAME 函数，并统计每个“星期 x”出现的次数。最终结果如下所示。

with x (start_date,end_date)
as (
select start_date,
       start_date + 1 year end_date
  from (
select (current_date -
        dayofyear(current_date) day)
        +1 day as start_date
  from t1
       ) tmp
 union all
select start_date + 1 day, end_date
  from x
 where start_date + 1 day < end_date
)
select dayname(start_date),count(*)
  from x
 group by dayname(start_date)
　
START_DATE   COUNT(*)
---------  ----------
FRIDAY             52
MONDAY             52
SATURDAY           53
SUNDAY             52
THURSDAY           52
TUESDAY            52
WEDNESDAY          52

MySQL
本解决方案结合 T500 表执行 SELECT 查询，为一年中的每一个日期生成单独的一行数据。第 4 行的命令用于生成当前年份的第一天。它的做法是调用 CURRENT_DATE 函数得到年份，然后在后面附加上月份和天（遵守 MySQL 默认的日期格式）。结果如下所示。

select concat(year(current_date),'-01-01')
  from t1
　
START_DATE
-----------
01-JAN-2005

现在有了当前年份第一天的日期，调用 DATEADD 函数将其与 T500.ID 逐一相加以生成一年里的每一天。调用 DATE_FORMAT 函数能够返回每一个日期是星期几。为了以 T500 表为基础生成足够多的行，要先找出当前年份第一天和下一年度第一天之间的差值，并生成与之相等数目的行（应该是 365 行或者 366 行）。部分查询结果如下所示。

select date_format(
          date_add(
              cast(
            concat(year(current_date),'-01-01')
                   as date),
                   interval t500.id-1 day),
                   '%W') day
  from t500
 where t500.id <= datediff(
                      cast(
                    concat(year(current_date)+1,'-01-01')
                           as date),
                      cast(
                    concat(year(current_date),'-01-01')
                           as date))
　
DAY
-----------
01-JAN-2005
02-JAN-2005
03-JAN-2005
...
29-JAN-2005
30-JAN-2005
31-JAN-2005
...
01-DEC-2005
02-DEC-2005
03-DEC-2005
...
29-DEC-2005
30-DEC-2005
31-DEC-2005

现在有了当前年份每一天的日期了，接下来要调用 DAYNAME 函数得到每一个日期是星期几，并统计每个“星期 x”出现的次数。最终结果如下所示。

select date_format(
          date_add(
              cast(
            concat(year(current_date),'-01-01')
                   as date),
                   interval t500.id-1 day),
                   '%W') day,
       count(*)
  from t500
 where t500.id <= datediff(
                      cast(
                    concat(year(current_date)+1,'-01-01')
                           as date),
                      cast(
                    concat(year(current_date),'-01-01')
                           as date))
 group by date_format(
             date_add(
                 cast(
               concat(year(current_date),'-01-01')
                      as date),
                      interval t500.id-1 day),
                      '%W')
　
DAY         COUNT(*)
--------- ----------
FRIDAY            52
MONDAY            52
SATURDAY          53
SUNDAY            52
THURSDAY          52
TUESDAY           52
WEDNESDAY         52

Oracle
有两种解决方案：结合 T500 表（数据透视表）执行 SELECT 查询，或者使用 CONNECT BY 和 WITH 递归查询，都能为当前年份的每一个日期生成单独的一行数据。使用 TRUNC 函数把当前的系统日期转换为当前年份的第一天。
对于 CONNECT BY 和 WITH 解决方案，我们利用临时的 LEVEL 列生成从 1 开始的数字序列。为了生成足够多行的数据，根据当前年份第一天和下一年度第一天之间的差值（应该是 365 天或者 366 天）筛选 ROWNUM 或者 LEVEL。下一步就是在当前年份第一天的基础上依次加上 ROWNUM 或者 LEVEL。部分查询结果如下所示。

/* Oracle 9i及后续版本 */
with x as (
select level lvl
  from dual
 connect by level <= (
  add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 )
)
select trunc(sysdate,'y')+lvl-1
  from x

对于使用数据透视表的解决方案，我们可以使用任何行数超过 366 行的表或者视图。由于 Oracle 支持 ROWNUM，我们并不需要一个从 1 开始递增的表。在下面的例子中，我们利用数据透视表 T500 来返回当前年份的每一天。

/* Oracle 8i及更早版本 */
select trunc(sysdate,'y')+rownum-1 start_date
  from t500
 where rownum <= (add_months(trunc(sysdate,'y'),12)
                  - trunc(sysdate,'y'))
　
START_DATE
-----------
01-JAN-2005
02-JAN-2005
03-JAN-2005
...
29-JAN-2005
30-JAN-2005
31-JAN-2005
...
01-DEC-2005
02-DEC-2005
03-DEC-2005
...
29-DEC-2005
30-DEC-2005
31-DEC-2005

不论是哪种解决方案，最终我们必须调用 TO_CHAR 函数获取每一个日期分别是星期几，然后统计每个“星期 x”出现的次数。最终结果如下所示。

/* Oracle 9i及后续版本 */
with x as (
select level lvl
  from dual
 connect by level <= (
   add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 )
)
select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)
  from x
 group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')
　
 /* Oracle 8i及更早版本 */
select to_char(trunc(sysdate,'y')+rownum-1,'DAY') start_date,
       count(*)
  from t500
 where rownum <= (add_months(trunc(sysdate,'y'),12)
                  - trunc(sysdate,'y'))
 group by to_char(trunc(sysdate,'y')+rownum-1,'DAY')
　
START_DATE   COUNT(*)
---------- ----------
FRIDAY             52
MONDAY             52
SATURDAY           53
SUNDAY             52
THURSDAY           52
TUESDAY            52
WEDNESDAY          52

PostgreSQL
首先调用 DATE_TRUNC 函数获取当前的年份（如下所示，由于针对 T1 做查询，因而只返回一行结果）。

select cast(
         date_trunc('year',current_date)
       as date) as start_date
  from t1
　
START_DATE
-----------
01-JAN-2005

然后，针对一个至少有 366 行的数据源（任何表达式均可）执行 SELECT 查询。本解决方案采用了 GENERATE_SERIES 函数作为数据源。当然，这里也可以用 T500 表。然后，在当前年份第一天的基础上逐次加上 1，直到把一年中的每一天都作为单独的一行返回（如下所示）。

select cast( date_trunc('year',current_date)
               as date) + gs.id-1 as start_date
  from generate_series (1,366) gs(id)
 where gs.id <= (cast
                  ( date_trunc('year',current_date) +
                       interval '12 month' as date) -
 cast(date_trunc('year',current_date)
                  as date))
　
START_DATE
-----------
01-JAN-2005
02-JAN-2005
03-JAN-2005
...
29-JAN-2005
30-JAN-2005
31-JAN-2005
...
01-DEC-2005
02-DEC-2005
03-DEC-2005
...
29-DEC-2005
30-DEC-2005
31-DEC-2005

最后，调用 TO_CHAR 函数获取每一个日期分别是星期几，然后统计每个“星期 x”出现的次数。最终的结果如下所示。

select to_char(
          cast(
    date_trunc('year',current_date)
                as date) + gs.id-1,'DAY') as start_dates,
       count(*)
  from generate_series(1,366) gs(id)
 where gs.id <= (cast
                  ( date_trunc('year',current_date) +
                       interval '12 month' as date) -
     cast(date_trunc('year',current_date)
                      as date))
 group by to_char(
             cast(
       date_trunc('year',current_date)
          as date) + gs.id-1,'DAY')
　
START_DATE   COUNT(*)
---------- ----------
FRIDAY             52
MONDAY             52
SATURDAY           53
SUNDAY             52
THURSDAY           52
TUESDAY            52
WEDNESDAY          52

SQL Server
WITH 递归查询视图 X 里的内嵌视图 TMP 返回当前年份的第一天，如下所示。

select cast(
       cast(year(getdate()) as varchar) + '-01-01'
            as datetime) start_date
  from t1
　
START_DATE
-----------
01-JAN-2005

得到当前年份的第一天之后，再为 START_DATE 加上一年，这样我们就知道了开始日期和结束日期。我们需要知道这两个日期，因为需要生成这一年中的每一天。START_DATE 和 END_DATE 如下所示。

select start_date,
        dateadd(year,1,start_date) end_date
  from (
select cast(
       cast(year(getdate()) as varchar) + '-01-01'
            as datetime) start_date
  from t1
       ) tmp
　
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006

下一步，为 START_DATE 加上一天，如此循环往复，直到它等于 END_DATE。如下展示了 WITH 递归查询视图 X 返回的结果集的一部分。

with x (start_date,end_date)
 as (
 select start_date,
        dateadd(year,1,start_date) end_date
   from (
 select cast(
        cast(year(getdate()) as varchar) + '-01-01'
             as datetime) start_date
   from t1
        ) tmp
 union all
 select dateadd(day,1,start_date), end_date
   from x
  where dateadd(day,1,start_date) < end_date
 )
 select * from x
 OPTION (MAXRECURSION 366)
　
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006
02-JAN-2005 01-JAN-2006
03-JAN-2005 01-JAN-2006
...
29-JAN-2005 01-JAN-2006
30-JAN-2005 01-JAN-2006
31-JAN-2005 01-JAN-2006
...
01-DEC-2005 01-JAN-2006
02-DEC-2005 01-JAN-2006
03-DEC-2005 01-JAN-2006
...
29-DEC-2005 01-JAN-2006
30-DEC-2005 01-JAN-2006
31-DEC-2005 01-JAN-2006

最后一步是针对 WITH 递归查询视图 X 的返回结果调用 DAYNAME 函数，并统计每个“星期 x”出现的次数。最终结果如下所示。

with x(start_date,end_date)
 as (
 select start_date,
        dateadd(year,1,start_date) end_date
   from (
 select cast(
        cast(year(getdate()) as varchar) + '-01-01'
             as datetime) start_date
   from t1
        ) tmp
  union all
 select dateadd(day,1,start_date), end_date
   from x
  where dateadd(day,1,start_date) < end_date
 )
 select datename(dw,start_date), count(*)
   from x
  group by datename(dw,start_date)
 OPTION (MAXRECURSION 366)
　
START_DATE   COUNT(*)
---------  ----------
FRIDAY             52
MONDAY             52
SATURDAY           53
SUNDAY             52
THURSDAY           52
TUESDAY            52
WEDNESDAY          52