LeetCode（数据库）- 报告系统状态的连续日期

题目链接：点击打开链接

题目大意：略。

解题思路：解决方案(2)代码看似多，但是思路很清晰，一步一步做下来；解决方案(1)变量 @id 中间虽然使用了 UNION ALL，但是还是会继续上面的数值接着来，而不是重新从 0 开始噢。

AC 代码

-- 解决方案(1)
SELECT period_state, MIN(date) AS start_date, MAX(date) AS end_date
FROM
( 
SELECT fail_date AS date, 
'failed' AS period_state,
IF(DATEDIFF(@pre, @pre := fail_date) = -1, @id, @id := @id+1) AS id
FROM Failed a1, (SELECT @id :=0, @pre := NULL) b1
UNION ALL
SELECT success_date AS date, 
'succeeded' AS period_state,
IF(DATEDIFF(@pre, @pre := success_date) = -1, @id, @id := @id+1) AS id
FROM Succeeded a2, (SELECT @id :=0, @pre := NULL) b2
) t3
WHERE date BETWEEN "2019-01-01" AND "2019-12-31"
GROUP BY t3.id
ORDER BY start_date

-- 解决方案(2)
WITH t1 AS(SELECT CAST(@num := @num + IF(@pre IS NOT NULL AND DATEDIFF(success_date, @pre) = 1, 0, 1) AS SIGNED) rk, @pre := success_date dt, 1 mark
FROM Succeeded, (SELECT @pre:=null, @num:=0) init
WHERE success_date BETWEEN '2019-01-01' AND '2019-12-31'),

t2 AS(SELECT rs2.minn dt1, rs1.maxn dt2, rs1.rk, rs1.mark
FROM 
(SELECT MAX(dt) maxn, rk, mark
FROM t1
GROUP BY rk) rs1
JOIN
(SELECT MIN(dt) minn, rk, mark
FROM t1
GROUP BY rk) rs2
ON rs1.rk = rs2.rk),

t3 AS(SELECT CAST(@num := @num + IF(@pre IS NOT NULL AND DATEDIFF(fail_date, @pre) = 1, 0, 1) AS SIGNED) rk, @pre := fail_date dt, 0 mark
FROM Failed, (SELECT @pre:=null, @num:=0) init
WHERE fail_date BETWEEN '2019-01-01' AND '2019-12-31'),

t4 AS(SELECT rs2.minn dt1, rs1.maxn dt2, rs1.rk, rs1.mark
FROM 
(SELECT MAX(dt) maxn, rk, mark
FROM t3
GROUP BY rk) rs1
JOIN
(SELECT MIN(dt) minn, rk, mark
FROM t3
GROUP BY rk) rs2
ON rs1.rk = rs2.rk)

SELECT IF(mark = 0, 'failed', 'succeeded') period_state, dt1 start_date, dt2 end_date
FROM (SELECT * FROM t2 UNION ALL SELECT * FROM t4) RS
ORDER BY dt1