原题链接 https://www.modb.pro/issue/8730
出个题，求工作时长
题面
表 A 记录了 A、B、C三人连续工作的开始时间和结束时间，如果结束时间为空，则表示仍然连续工作
name start_date end_date
A 2021-06-30 2021-07-02
A 2021-07-05 2021-07-10
A 2021-07-30 2021-07-31
A 2021-08-02
B 2021-06-30 2021-08-02
B 2021-08-05
C 2021-03-05 2021-03-10
求：
今天是9月1日，该发工资了，求A、B、C三人分别在6月、7月、8月工作的天数，方便结算工资？
name month days
A 6月 1
A 7月 10
A 8月 30
B 6月 1
B 7月 31
B 8月 29
C 6月 0
C 7月 0
C 8月 0
SQL Server解法
with cte1 as
(
select 'A' as name,'2021-06-30' as start_date,'2021-07-02' as end_date union all
select 'A' as name,'2021-07-05' as start_date,'2021-07-10' as end_date union all
select 'A' as name,'2021-07-30' as start_date,'2021-07-31' as end_date union all
select 'A' as name,'2021-08-02' as start_date,'' as end_date union all
select 'B' as name,'2021-06-30' as start_date,'2021-08-02' as end_date union all
select 'B' as name,'2021-08-05' as start_date,'' as end_date union all
select 'C' as name,'2021-03-05' as start_date,'2021-03-10' as end_date
),
cte2 as
(
select DATEADD(DAY,number,'2021-06-01') as datekey from master.dbo.spt_values where type = 'p'
),
cte3 as
(
select * from cte2
cross join (select distinct [name] from cte1)as t1
where datekey >'2021-05-31' and datekey<'2021-09-01'
)
,cte4 as
(
select a.name,t.datekey from cte1 a
outer apply (select datekey from cte2 b where b.datekey >= a.start_date and b.datekey <= case when a.end_date = '' then '2021-09-01' else a.end_date end
and datekey<'2021-09-01') as t
group by a.name,t.datekey
)
select convert(varchar(7),a.datekey,120),a.name,count(b.name) as days from cte3 a
left join cte4 b on a.datekey = b.datekey and a.name = b.name
group by convert(varchar(7),a.datekey,120),a.name
order by a.name,convert(varchar(7),a.datekey,120)
结果
MONTH NAME DAYS
2021-06 A 1
2021-07 A 10
2021-08 A 30
2021-06 B 1
2021-07 B 31
2021-08 B 29
2021-06 C 0
2021-07 C 0
2021-08 C 0
MySQL解法
with cte1 as
(
select 'A' as name,'2021-06-30' as start_date,'2021-07-02' as end_date union all
select 'A' as name,'2021-07-05' as start_date,'2021-07-10' as end_date union all
select 'A' as name,'2021-07-30' as start_date,'2021-07-31' as end_date union all
select 'A' as name,'2021-08-02' as start_date,'' as end_date union all
select 'B' as name,'2021-06-30' as start_date,'2021-08-02' as end_date union all
select 'B' as name,'2021-08-05' as start_date,'' as end_date union all
select 'C' as name,'2021-03-05' as start_date,'2021-03-10' as end_date
),
cte11 as
(
select row_number() over(order by table_name,column_name) as rowno from Information_schema.COLUMNS
)
,cte2 as
(
select DATE_ADD('2021-06-01', INTERVAL rowno DAY ) as datekey from cte11
),
cte3 as
(
select * from cte2
cross join (select name from cte1 group by name)as t1
where datekey >'2021-05-31' and datekey<'2021-09-01'
)
,cte4 as
(
select a.name,t.datekey from cte1 a
LEFT JOIN LATERAL (select datekey from cte2 b where b.datekey >= a.start_date and b.datekey <= case when a.end_date = '' then '2021-09-01' else a.end_date end
and datekey<'2021-09-01') as t on 1=1
group by a.name,t.datekey
)
select month(a.datekey),a.name,count(b.name) as days from cte3 a
left join cte4 b on a.datekey = b.datekey and a.name = b.name
group by month(a.datekey),a.name
order by a.name,month(a.datekey)
month name days
6 A 1
7 A 10
8 A 30
6 B 1
7 B 31
8 B 29
6 C 0
7 C 0
8 C 0
mysql 8.0增加了 LATERAL 关键字可以实现 在关联中，左表引表右表，或右表引用左表。
