mysql之SQL计算连胜和连败

如果我有一个如下所示的 SQL 表，我如何计算当前的连胜或连败(以及按赛季对连胜或连败进行分组/重置)。我想更新表格并为每条记录填写条纹。

所以对于#1 的连胜数将是“-1”，#2 将是“1”，#3 将是“2”，但一旦我们下降到#7，它将再次重置为“1”。 (+1 表示“赢了 1 场”，-1 表示“输了 1 场”，等等)

ID    team    date         Result    season     streak 
1     76ers   2000-01-01   Loss      2000       Null 
2     76ers   2000-01-05   Win       2000       Null 
3     76ers   2000-01-08   Win       2000       Null 
4     Lakers  2000-01-03   Loss      2000       Null 
5     Lakers  2000-01-07   Loss      2000       Null 
6     Lakers  2000-01-01   Win       2000       Null 
 
7     76ers   2002-03-01   Win       2001       Null 
8     76ers   2002-03-05   Win       2001       Null 
9     76ers   2002-03-08   Loss      2001       Null 
10    Lakers  2002-03-03   Loss      2001       Null 
11    Lakers  2002-03-07   Loss      2001       Null 
12    Lakers  2002-03-01   Win       2001       Null 

请您参考如下方法:

对于每场比赛，统计前一场比赛结果相同的比赛数，中间没有比赛结果相反的比赛。将结果存储在临时表中:

CREATE TEMPORARY TABLE STREAK_TABLE 
SELECT 
    ID, 
    ( 
        SELECT 1 + COUNT(*)                 -- Earlier games with the same result, team and season. 
        FROM YOUR_TABLE T2 
        WHERE 
            T1.Result = T2.Result 
            AND T1.team = T2.team 
            AND T1.season = T2.season 
            AND T1.date > T2.date 
            AND NOT EXISTS ( 
                SELECT *                    -- The games in between, with the same team and season but opposite result. 
                FROM YOUR_TABLE T3 
                WHERE 
                    T2.Result <> T3.Result 
                    AND T1.team = T3.team 
                    AND T1.season = T3.season 
                    AND T3.date BETWEEN T2.date AND T1.date 
            ) 
    ) S 
FROM YOUR_TABLE T1 

然后，更新原始表(并否定过程中的连败):

UPDATE YOUR_TABLE 
SET streak = ( 
    SELECT CASE Result WHEN 'Win' THEN S ELSE -S END 
    FROM STREAK_TABLE 
    WHERE STREAK_TABLE.ID = YOUR_TABLE.ID 
) 

最后，清理临时表:

DROP TABLE STREAK_TABLE