Leetcode：1225. 报告系统状态的连续日期-连续问题

 题目：力扣

 

 

方法一：

• 将两表union，打上标记；
• 用行号与date相减的时间作为标记，标记一致的为连续的；
• 展示即可

select period_state,start_date,end_date 
from(
select type as period_state,diff, min(date) as start_date, max(date) as end_date
from
(
    select type, date, subdate(date,row_number()over(partition by type order by date)) as diff,
    row_number()over(partition by type order by date) as rn
    from
    (
        select 'failed' as type, fail_date as date from Failed
        union all
        select 'succeeded' as type, success_date as date from Succeeded
    ) a
)a
where date between '2019-01-01' and '2019-12-31'
group by type,diff
) t
order by start_date

select period_state, min(date) start_date, max(date) end_date
from
(select period_state,date,date_sub(date,interval row_number()over(partition by period_state order by date) day) cnt from
(select fail_date date,'failed' period_state from failed
union select success_date,'succeeded' period_state from succeeded) t
where date like'2019-%')c
group by period_state,cnt
order by start_date

 

方法二：with函数

with tb as (select 'failed' as state, fail_date as all_date from failed 
union 
select 'succeeded' as state, success_date as all_date from succeeded)

select state as period_state, min(all_date) as start_date, max(all_date) as end_date
from (select *, rank() over(order by all_date)- rank() over (partition by state order by all_date) as diff from tb where all_date between '2019-01-01' and '2019-12-31') as temp
group by state, diff
order by start_date