【LeetCode 3586】MySQL 用户变量编程解法

Problem: 3586.

目录

• 思路
• Code

思路

MySQL 用户变量编程解法

Code

SELECT patient_id, patient_name, age, recovery_time
FROM (
    SELECT
        patient_id, 
        -- ↓↓↓↓↓↓↓只需修改里面的逻辑就可以。注意里面的语句是“顺序执行”的 ↓↓↓↓↓↓↓↓↓
        @pos_date := IF(@current_id = patient_id, 
                        IF(result = 'Positive' AND @pos_date IS NULL, test_date, @pos_date), -- 如果是同一用户且positive：如果@pos_date 非 null (已更新)，更新@pos_date；否则保持
                        IF(result = 'Positive', test_date, NULL)) AS pos_date,   -- 如果处理到了新用户且positive：更新@pos_date；否则null
        @neg_date := IF(@current_id = patient_id,
                       IF(result = 'Negative' AND @pos_date IS NOT NULL AND @neg_date IS NULL, test_date, @neg_date), -- 如果是同一用户且negative：如果@pos_date 非 null (之前pos过)，更新@neg_date ；否则保持
                       IF(result = 'Negative' AND @pos_date IS NOT NULL, test_date, NULL)) AS neg_date,  -- 如果处理到了新用户且negative：如果@pos_date 非 null (之前pos过)，更新 @neg_date ； 否则null
        @recovery_time := IF(@neg_date IS NOT NULL AND @pos_date IS NOT NULL, 
                            DATEDIFF(@neg_date, @pos_date), NULL) AS recovery_time,
        @has_recovered := IF(@recovery_time IS NOT NULL, 1, 0) AS has_recovered,  --  是否有过 【 pos后（不必连续）接neg 】 序列模式
        @current_id := patient_id AS dummy,
        -- ↑↑↑↑↑↑↑↑↑↑只需修改里面的逻辑就可以。↑↑↑↑↑↑↑
        patient_name,
        age
    FROM (
        SELECT c.*, p.patient_name, p.age
        FROM covid_tests c
        JOIN patients p ON c.patient_id = p.patient_id
        ORDER BY c.patient_id, c.test_date
    ) ordered_data,
    (SELECT @current_id := 0, @pos_date := NULL, @neg_date := NULL, @recovery_time := NULL, @has_recovered := 0) init_vars
) recovery_data
WHERE has_recovered = 1
GROUP BY patient_id
ORDER BY recovery_time ASC, patient_name ASC;