SQL计算一年内有多少天是星期一、二、三、四、五、六、日

最新推荐文章于 2023-03-23 16:53:57 发布

原创最新推荐文章于 2023-03-23 16:53:57 发布 · 870 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #sql

SQL 专栏收录该内容

2 篇文章

订阅专栏

该博客展示了如何使用SQL Server和MySQL查询一年中每一天是星期几，并分别统计了星期日到星期六的天数。通过创建临时表和使用CASE语句，对指定年份的日期进行迭代，计算每个星期的天数。

SqlServer

declare @a varchar (20)
select @a='2021'
if object_id(N'tempdb..#t',N'U') is not null
drop table #t
create table #t (rq datetime)
declare @b datetime
select @b=(select DATEADD (year, DATEDIFF(year,0,@a),0))
while @b<(select DATEADD(year, DATEDIFF (year,0 ,@a),365))
begin
insert into #t values ( @b)
select @b= (select DATEADD (day ,1,@b) )
end
select
sum (case when datename (weekday,rq)='星期日' then 1 else 0 end) as 星期日天数,
sum (case when datename (weekday,rq)='星期一' then 1 else 0 end) as 星期一天数,
sum (case when datename (weekday,rq)='星期二' then 1 else 0 end) as 星期二天数,
sum (case when datename (weekday,rq)='星期三' then 1 else 0 end) as 星期三天数,
sum (case when datename (weekday,rq)='星期四' then 1 else 0 end) as 星期四天数,
sum (case when datename (weekday,rq)='星期五' then 1 else 0 end) as 星期五天数,
sum (case when datename (weekday,rq)='星期六' then 1 else 0 end) as 星期六天数
from #t

mysql

set @year_s='2020-01-01';
select
sum(case when ttl.week_num=l then l else 0 end) '周日天数',
sum(case when ttl.week_num=2 then l else 0 end) '周一天数',
sum(case when ttl.week_num=3 then l else 0 end) '周二天数',
sum (case when ttl.week_num=4 then l else 0 end)'周三天数',
sum (case when ttl.week_num=5 then l else 0 end)'周四天数',
sum (case when ttl.week_num=6 then 1 else o end)'周五天数',
sum (case when ttl.week_num=7 then l else 0 end)'周六天数'
from
(
select
date_add (@year_s, interval tl.help_topic_id day) date_s,
dayofweek(date_add(@year_s,interval tl.help_topic_id day)) week
from
mysql.help_topic tl
where
tl.help_topic_id<=366
)ttl
where date_s<date_add(@year_s,interval l year);