1225. 报告系统状态的连续日期

排序法通用逻辑：利用需要排序的列与row_number之差进行分组

select period_state, min(dt) start_date, max(dt) end_date from
(
select *, subdate(dt,rank() over(partition by period_state order by dt)) dif from
(
select 'failed' period_state, fail_date dt from Failed where year(fail_date) = 2019
union
select 'succeeded' period_state, success_date dt from Succeeded where year(success_date) = 2019
) temp
) temp1 
group by period_state,dif 
order by start_date

官方题解，使用临时变量，思路与上面是一样的

SELECT period_state, MIN(date) as start_date, MAX(date) as end_date
FROM (
    SELECT
        success_date AS date,
        "succeeded" AS period_state,
        IF(DATEDIFF(@pre_date, @pre_date := success_date) = -1, @id, @id := @id+1) AS id 
    FROM Succeeded, (SELECT @id := 0, @pre_date := NULL) AS temp
    UNION
    SELECT
        fail_date AS date,
        "failed" AS period_state,
        IF(DATEDIFF(@pre_date, @pre_date := fail_date) = -1, @id, @id := @id+1) AS id 
    FROM Failed, (SELECT @id := 0, @pre_date := NULL) AS temp
) T  WHERE date BETWEEN "2019-01-01" AND "2019-12-31"
GROUP BY T.id
ORDER BY start_date ASC