SQL技术问询：如何查询各赛季获胜场次最多的球队

嘿，你已经完成了最核心的统计步骤，现在只需要把各队的胜场数据和对应赛季的最高胜场数关联起来就行。下面给你两种常用的解决方案，适配不同的SQL场景：

方法一：用窗口函数（推荐，简洁且支持并列） #

现代SQL几乎都支持窗口函数，用RANK()或者DENSE_RANK()可以轻松处理并列第一的情况（比如某赛季有两支球队胜场相同且都是最多）：

WITH team_season_wins AS (
    -- 先统计每个球队每个赛季的获胜场次
    SELECT 
        winner AS team,
        season,
        COUNT(*) AS win_count
    FROM matches
    GROUP BY winner, season
),
ranked_teams AS (
    -- 按赛季分组，给各队的胜场数排名
    SELECT 
        team,
        season,
        win_count,
        -- RANK()会给并列的球队相同排名，比如两个第一都是1
        RANK() OVER (PARTITION BY season ORDER BY win_count DESC) AS win_rank
    FROM team_season_wins
)
-- 筛选出每个赛季排名第一的球队
SELECT team, season, win_count
FROM ranked_teams
WHERE win_rank = 1;

如果你的业务逻辑中不需要保留并列结果（比如只取任意一支第一的球队），可以把RANK()换成ROW_NUMBER()，但RANK()更符合“获胜场次最多”的实际场景。

方法二：用子查询关联（兼容老版本SQL） #

如果你的数据库不支持窗口函数（比如MySQL 5.7及更早版本），可以用子查询关联的方式，把你已经写好的“赛季最大胜场”结果和各队的胜场数据匹配：

WITH team_season_wins AS (
    SELECT 
        winner AS team,
        season,
        COUNT(*) AS win_count
    FROM matches
    GROUP BY winner, season
),
season_max_wins AS (
    -- 这就是你已经写好的查询，得到每个赛季的最大胜场数
    SELECT 
        season,
        MAX(win_count) AS max_win_count
    FROM team_season_wins
    GROUP BY season
)
-- 通过赛季和胜场数关联，找到对应球队
SELECT tsw.team, tsw.season, tsw.win_count
FROM team_season_wins tsw
JOIN season_max_wins smw 
    ON tsw.season = smw.season 
    AND tsw.win_count = smw.max_win_count;

不支持CTE的数据库适配 #

如果你的数据库连CTE都不支持，可以把CTE替换成嵌套子查询：

SELECT tsw.team, tsw.season, tsw.win_count
FROM (
    SELECT winner AS team, season, COUNT(*) AS win_count
    FROM matches
    GROUP BY winner, season
) tsw
JOIN (
    SELECT season, MAX(win_count) AS max_win_count
    FROM (
        SELECT winner, season, COUNT(*) AS win_count
        FROM matches
        GROUP BY winner, season
    ) temp
    GROUP BY season
) smw 
    ON tsw.season = smw.season 
    AND tsw.win_count = smw.max_win_count;

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