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

码龄10年

关注

原创最新推荐文章于 2023-06-09 14:01:36 发布 890 阅读

次日、3日、7日、14日、30日、90日留存计算SQL命令（实测有效版）以前找过多个不同版本的留存SQL命令，大多无法让人满意，现提供一个真正实用的版本说明： role_login_back：表名字 event_time：登录日期时间，例如：2019-12-06 10:02:08 device_id：设备ID 数据库表role_login_back的字段： /*计算某日新增登录设备的次日、3日、7日、14日、30日、90日留存率*/ SELECT log_day... 阅读详情

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);