GROUP BY时选取球员最新所属球队的SQL优化问题

问题根源 #

原SQL中，GROUP BY playerID, season 后直接选取team字段，但team既不在分组字段中也未做聚合处理，多数数据库会随机返回分组内某条记录的team值，无法保证是球员该赛季最后效力的球队。

解决方案 #

以下提供两种通用可行的写法，适配不同数据库版本：

方案1：使用窗口函数（推荐，支持MySQL 8.0+、PostgreSQL、SQL Server等）

利用窗口函数为每个球员的赛季记录按比赛日期倒序排名，取排名第一的记录（即最新比赛）的球队，同时计算赛季总得分：

WITH player_season_data AS (
    SELECT 
        playerID,
        season,
        team,
        points,
        -- 按球员+赛季分组，日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY playerID, season ORDER BY date DESC) AS record_rank,
        -- 计算该球员该赛季的总得分
        SUM(points) OVER (PARTITION BY playerID, season) AS total_points
    FROM gamestats_players
)
SELECT playerID, season, team, total_points
FROM player_season_data
WHERE record_rank = 1
ORDER BY playerID, season;

方案2：子查询关联（适配旧版数据库）

先获取每个球员每个赛季的最新比赛日期，再关联原表找到对应球队，最后结合赛季总得分统计：

SELECT 
    latest_team.playerID,
    latest_team.season,
    latest_team.team,
    total_stats.total_points
FROM (
    -- 获取每个球员赛季的最新球队
    SELECT g.playerID, g.season, g.team
    FROM gamestats_players g
    WHERE g.date = (
        SELECT MAX(date) 
        FROM gamestats_players 
        WHERE playerID = g.playerID AND season = g.season
    )
) latest_team
JOIN (
    -- 统计每个球员赛季的总得分
    SELECT playerID, season, SUM(points) AS total_points
    FROM gamestats_players
    GROUP BY playerID, season
) total_stats 
ON latest_team.playerID = total_stats.playerID 
AND latest_team.season = total_stats.season
ORDER BY latest_team.playerID, latest_team.season;

验证结果 #

针对示例数据执行上述SQL后，会得到正确结果：

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