题目地址

https://leetcode.cn/problems/longest-winning-streak/description/

题目描述

选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。

编写解决方案来计算每个参赛选手最多的连胜数。

结果可以以 任何顺序 返回。

代码

WITH t1  AS (
    SELECT
        #------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的-------------------------------
        match_day,
        player_id,
        (case when result="Win" and @prev_result="Win" AND player_id = @prev_player_id then @consec_matches := @consec_matches + 1
        when result="Win" AND player_id = @prev_player_id then @consec_matches := 1
        when result="Win" then @consec_matches := 1
        else @consec_matches := 0 end) 
        AS consec_matches,

        @prev_date := match_day,
        @prev_player_id := player_id,
        @prev_result:=result
        #-------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的------------------------------
    FROM
        (SELECT @prev_date := NULL, @prev_player_id := NULL, @consec_matches := 1,@prev_result:=NULL ) vars,
        (SELECT match_day, player_id,result FROM Matches ORDER BY player_id , match_day) ordered_dates
)

SELECT player_id,max(consec_matches) as  longest_streak
from t1
group by player_id