极客教程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