题目地址

https://leetcode.cn/problems/merge-overlapping-events-in-the-same-hall/

代码

# Write your MySQL query statement below

with t2 as(
    select 
    *

    # ----只需要改动这里的逻辑，其他不要动。注意里面的语句是“顺序执行的”-----------

    -- 如果切换hall_id了，一些变量需要改变
    ,(case when (@prev_hall_id is null or @prev_hall_id<>hall_id) then @farest_interval_start:=start_day end)
    ,(case when (@prev_hall_id is null or @prev_hall_id<>hall_id) then @farest_interval_end:=end_day end)
    ,(case when (@prev_hall_id is null or @prev_hall_id<>hall_id) then @interval_id := @interval_id +1 end)
    

    -- 如果新区间的开始要晚于维护区间的结尾，那么开启新区间
    , (case when @farest_interval_end < start_day then @interval_id := @interval_id +1 else @interval_id end) as interval_id
    , (case when @farest_interval_end < start_day then @farest_interval_start:=start_day end)
    , (case when @farest_interval_end < start_day then @farest_interval_end:=end_day end)
    


    -- 如果新区间的开始不晚于维护区间的结尾，可能会发生区间的合并。由于我们已经按照start_day asc, end_day asc排序了。所以只可能更新@farest_interval_end
    ,(case when @farest_interval_end < start_day then "whatever" else
        (case when @farest_interval_end <= end_day then @farest_interval_end:= end_day end)
    end)
    

    , @farest_interval_start as farest_interval_start
    , @farest_interval_end as farest_interval_end
    , @prev_hall_id := hall_id

    # ----只需要改动这里的逻辑，其他不要动。注意里面的语句是“顺序执行的”-----------

    from (select * from HallEvents order by hall_id asc, start_day asc, end_day asc)  sorted_HallEvents,
    (select @prev_hall_id := null, @farest_interval_start := null, @farest_interval_end := null, @interval_id := 0) vars
)


select hall_id, farest_interval_start as start_day
, farest_interval_end as end_day 
from 
(select *,
row_number() over(partition by interval_id order by start_day desc, end_day desc) as rn
from t2) tmp1
where rn=1
order by  hall_id, start_day, end_day