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

Love The Way You Lie 2022-09-10 04:24 86阅读 0赞

**题目链接：**[点击打开链接][Link 1]

**题目大意：**略。

**解题思路：**解决方案(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

[Link 1]: https://leetcode-cn.com/problems/report-contiguous-dates/