原題鏈接 https://www.modb.pro/issue/8730

出個題，求工作時長
題面
表 A 記錄了 A、B、C三人連續(xù)工作的開始時間和結束時間，如果結束時間為空，則表示仍然連續(xù)工作

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日，該發(fā)工資了，求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 關鍵字可以實現 在關聯(lián)中，左表引表右表，或右表引用左表。