526互联

Mysql 用户自定义变量，面向过程编程解决"连续天数"的问题

发布时间 2023-03-22 21:47:54作者: yhm138

题目地址

https://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd

代码

牛客网的Mysql是8.0，MySQL 用户变量在8.0.13后不可用。
这题仍然可以使用用户变量过程编程的方式解决"连续天数"的问题。

思路就是先按照author_id, answer_date排序，之后一行一行处理。

# 代码由chatgpt给出
# Keep in mind that MySQL user variables are deprecated since MySQL 8.0.13, so using window functions might be a better approach for future-proofing your query.

WITH answer_dates AS (
    SELECT
        answer_date,
        author_id,
        IF(answer_date = @prev_date + INTERVAL 1 DAY AND author_id = @prev_author_id, @consec_days := @consec_days + 1, @consec_days := 1) AS consec_days,
        @prev_date := answer_date,
        @prev_author_id := author_id
    FROM
        (SELECT @prev_date := NULL, @prev_author_id := NULL, @consec_days := 1) vars,
        (SELECT answer_date, author_id FROM answer_tb ORDER BY author_id, answer_date) ordered_dates
)


select t1.author_id,t2.author_level as author_level,
CAST(t1.max_consec_days AS UNSIGNED) AS days_cnt
from
(
    SELECT
        author_id,
        MAX(consec_days) AS max_consec_days
    FROM
        answer_dates
    GROUP BY
        author_id
    HAVING
        max_consec_days >= 3
)t1
join author_tb t2
on t2.author_id=t1.author_id