MySQL基于比赛结果表获取球队当前Winning Streak问题求助

针对你需要基于最新比赛结果计算各球队当前连胜（Winning Streak）的需求，这里提供两种适配MySQL的方案，分别适用于MySQL 8.0+（支持窗口函数）和MySQL 5.7及以下版本（使用变量）。

核心思路 #

当前连胜的定义是：从球队最新一场比赛开始往前数，连续获得胜利（Result='H'）的场次，直到遇到非胜利结果（A或D）为止；如果最新一场比赛不是胜利，连胜数为0。

我们可以通过"分组连续胜利段"的方式实现：将最新的连续胜利场次归为同一个分组，统计该分组的行数即可得到当前连胜数。

方案1：MySQL 8.0+（窗口函数实现） #

窗口函数让逻辑更清晰易读，推荐使用：

测试数据准备 #

首先创建测试表并插入你提供的数据：

CREATE TABLE IF NOT EXISTS team_results (
    TeamID INT,
    Result CHAR(1),
    Date DATE
);

INSERT INTO team_results VALUES
(25, 'A', '2017-12-02'),
(25, 'H', '2017-12-16'),
(25, 'D', '2017-12-22'),
(25, 'D', '2018-01-03'),
(25, 'H', '2018-01-20'),
(28, 'D', '2017-12-09'),
(28, 'D', '2017-12-23'),
(28, 'H', '2018-01-01'),
(28, 'H', '2018-01-20'),
(58, 'H', '2017-12-02'),
(58, 'A', '2017-12-16'),
(58, 'H', '2017-12-23'),
(58, 'H', '2018-01-01'),
(58, 'D', '2018-01-20'),
(61, 'D', '2017-12-03'),
(61, 'A', '2017-12-17'),
(61, 'D', '2017-12-26'),
(61, 'H', '2017-12-30'),
(61, 'H', '2018-01-14');

计算当前连胜的SQL #

WITH ranked_results AS (
    SELECT 
        TeamID,
        Result,
        Date,
        -- 标记非获胜场次
        CASE WHEN Result != 'H' THEN 1 ELSE 0 END AS non_win_flag,
        -- 按球队分组、日期倒序计算累积和，划分连胜段
        SUM(CASE WHEN Result != 'H' THEN 1 ELSE 0 END) OVER (
            PARTITION BY TeamID 
            ORDER BY Date DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS streak_group
    FROM team_results
)
SELECT 
    TeamID,
    -- 统计最新连胜段（streak_group=0）的场次
    COUNT(CASE WHEN streak_group = 0 THEN 1 END) AS current_winning_streak
FROM ranked_results
GROUP BY TeamID
ORDER BY TeamID;

逻辑解释 #

1. CTE ranked_results：
   • non_win_flag：将非胜利场次标记为1，胜利场次标记为0。
   • streak_group：通过累积和划分分组，从最新比赛开始，每遇到一场非胜利，分组ID就加1。这样最新的连续胜利场次会被归为streak_group=0的分组。
2. 最终统计：分组统计每个球队streak_group=0的行数，即为当前连胜数。如果最新一场不是胜利，streak_group从第一行就是1，统计结果为0。

方案2：MySQL 5.7及以下版本（变量实现） #

如果你的MySQL版本不支持窗口函数，可以使用用户变量来实现相同逻辑：

SELECT 
    TeamID,
    SUM(CASE WHEN streak_group = 0 THEN 1 ELSE 0 END) AS current_winning_streak
FROM (
    SELECT 
        TeamID,
        Result,
        Date,
        -- 动态更新分组ID：切换球队时重置，遇到非胜利时递增
        @group_id := CASE 
            WHEN @current_team != TeamID THEN 0
            WHEN Result != 'H' THEN @group_id + 1
            ELSE @group_id
        END AS streak_group,
        @current_team := TeamID
    FROM team_results
    -- 初始化变量
    CROSS JOIN (SELECT @current_team := -1, @group_id := 0) AS vars
    -- 按球队、日期倒序排序，确保最新比赛先处理
    ORDER BY TeamID, Date DESC
) AS ranked_results
GROUP BY TeamID
ORDER BY TeamID;

逻辑解释 #

• 使用@current_team跟踪当前处理的球队，@group_id跟踪当前分组ID。
• 按TeamID和Date DESC排序，确保同一球队的最新比赛优先处理。
• 当切换球队时，重置@group_id为0；遇到非胜利场次时，@group_id加1；胜利场次则保持当前分组ID。最终最新的连续胜利场次会在streak_group=0的分组中。

执行结果 #

两种方案都会得到符合你预期的结果：

内容的提问来源于stack exchange，提问作者colic0