MySQL单查询实现赛事表取指定日期前数据并新增主客场连胜字段

适用MySQL 8.0+版本（支持窗口函数） #

WITH team_results AS (
    -- 拆分每场比赛为主队、客队两条参赛胜负记录
    SELECT 
        competition_id,
        home_team_id AS team_id,
        `date`,
        id AS match_id,
        CASE WHEN home_score > away_score THEN 1 ELSE 0 END AS is_win,
        CASE WHEN home_score < away_score THEN 1 ELSE 0 END AS is_loss
    FROM matches
    UNION ALL
    SELECT 
        competition_id,
        away_team_id AS team_id,
        `date`,
        id AS match_id,
        CASE WHEN away_score > home_score THEN 1 ELSE 0 END AS is_win,
        CASE WHEN away_score < home_score THEN 1 ELSE 0 END AS is_loss
    FROM matches
),
team_streak_groups AS (
    -- 按赛事、球队分组，用输球次数划分连胜区间：每次输球后区间号+1
    SELECT 
        *,
        SUM(is_loss) OVER (
            PARTITION BY competition_id, team_id 
            ORDER BY `date` ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS loss_group
    FROM team_results
),
team_streaks AS (
    -- 统计每个连胜区间内、当前比赛之前的赢球场次，即为当前连胜数
    SELECT 
        competition_id,
        team_id,
        match_id,
        SUM(is_win) OVER (
            PARTITION BY competition_id, team_id, loss_group 
            ORDER BY `date` ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS current_streak
    FROM team_streak_groups
)
-- 关联回原赛事表，获取主队、客队对应的连胜数
SELECT 
    m.*,
    hs.current_streak AS home_team_streak,
    `as`.current_streak AS away_team_streak
FROM matches m
LEFT JOIN team_streaks hs 
    ON m.competition_id = hs.competition_id 
    AND m.home_team_id = hs.team_id 
    AND m.id = hs.match_id
LEFT JOIN team_streaks `as` 
    ON m.competition_id = `as`.competition_id 
    AND m.away_team_id = `as`.team_id 
    AND m.id = `as`.match_id
WHERE m.`date` < '2019-02-23 00:00:00'
ORDER BY m.`date`;

逻辑说明 #

• 先将每场比赛拆分为主队、客队两条独立的参赛记录，分别标记每场比赛该球队的胜负状态
• 按赛事+球队维度对所有参赛记录排序，用历史输球次数作为分组标识，同一个分组内的所有比赛都是该球队最近一次输球之后参与的场次
• 统计每个分组内当前比赛之前的赢球场次，即为该球队参加当前赛事前的连胜数
• 最后将主队、客队的连胜数关联回原赛事表，过滤日期后得到最终结果

补充说明：如果平局需要中断连胜，只需要调整is_loss的判断逻辑，将平局场景也标记为is_loss=1即可。

适用MySQL 5.7版本（无窗口函数支持） #

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

SELECT 
    m.*,
    hs.current_streak AS home_team_streak,
    `as`.current_streak AS away_team_streak
FROM matches m
LEFT JOIN (
    SELECT 
        competition_id,
        team_id,
        match_id,
        SUM(is_win) AS current_streak
    FROM (
        SELECT 
            *,
            @loss_group := IF(@pre_team = team_id AND @pre_comp = competition_id, IF(is_loss = 1, @loss_group + 1, @loss_group), 0) AS loss_group,
            @pre_team := team_id,
            @pre_comp := competition_id
        FROM (
            SELECT * FROM (
                SELECT 
                    competition_id,
                    home_team_id AS team_id,
                    `date`,
                    id AS match_id,
                    CASE WHEN home_score > away_score THEN 1 ELSE 0 END AS is_win,
                    CASE WHEN home_score < away_score THEN 1 ELSE 0 END AS is_loss
                FROM matches
                UNION ALL
                SELECT 
                    competition_id,
                    away_team_id AS team_id,
                    `date`,
                    id AS match_id,
                    CASE WHEN away_score > home_score THEN 1 ELSE 0 END AS is_win,
                    CASE WHEN away_score < home_score THEN 1 ELSE 0 END AS is_loss
                FROM matches
            ) t ORDER BY competition_id, team_id, `date`
        ) t1, (SELECT @pre_team := NULL, @pre_comp := NULL, @loss_group := 0) vars
    ) t2
    GROUP BY competition_id, team_id, loss_group, match_id
) hs ON m.competition_id = hs.competition_id AND m.home_team_id = hs.team_id AND m.id = hs.match_id
LEFT JOIN (
    SELECT 
        competition_id,
        team_id,
        match_id,
        SUM(is_win) AS current_streak
    FROM (
        SELECT 
            *,
            @loss_group2 := IF(@pre_team2 = team_id AND @pre_comp2 = competition_id, IF(is_loss = 1, @loss_group2 + 1, @loss_group2), 0) AS loss_group,
            @pre_team2 := team_id,
            @pre_comp2 := competition_id
        FROM (
            SELECT * FROM (
                SELECT 
                    competition_id,
                    home_team_id AS team_id,
                    `date`,
                    id AS match_id,
                    CASE WHEN home_score > away_score THEN 1 ELSE 0 END AS is_win,
                    CASE WHEN home_score < away_score THEN 1 ELSE 0 END AS is_loss
                FROM matches
                UNION ALL
                SELECT 
                    competition_id,
                    away_team_id AS team_id,
                    `date`,
                    id AS match_id,
                    CASE WHEN away_score > home_score THEN 1 ELSE 0 END AS is_win,
                    CASE WHEN away_score < home_score THEN 1 ELSE 0 END AS is_loss
                FROM matches
            ) t ORDER BY competition_id, team_id, `date`
        ) t1, (SELECT @pre_team2 := NULL, @pre_comp2 := NULL, @loss_group2 := 0) vars
    ) t2
    GROUP BY competition_id, team_id, loss_group, match_id
) `as` ON m.competition_id = `as`.competition_id AND m.away_team_id = `as`.team_id AND m.id = `as`.match_id
WHERE m.`date` < '2019-02-23 00:00:00'
ORDER BY m.`date`;

内容的提问来源于stack exchange，提问作者Carlos Crespo Moreno