2023板球世界杯：查询各队单场得分最高球员的技术需求

原查询的问题在于，group by team_innings,batsman_name会将每个球员作为单独分组返回，最终结果是所有球员的得分排序，无法实现每队仅返回一名最高得分球员的需求。

调整方案：使用窗口函数（推荐） #

利用ROW_NUMBER()窗口函数，按队伍分组后对球员得分降序排序，标记出每队得分最高的球员（若有同分，会随机选其一；如需保留所有同分球员，可替换为RANK()或DENSE_RANK()）。

WITH team_top_batsman AS (
    SELECT 
        team_innings,
        batsman_name,
        runs,
        -- 按队伍分组，得分降序排序，标记每队的排名
        ROW_NUMBER() OVER (PARTITION BY team_innings ORDER BY runs DESC) AS rn
    FROM batting_summary
    -- 如果表中包含其他赛事数据，需添加2023世界杯的筛选条件，比如：
    -- WHERE tournament = '2023 Cricket World Cup'
)
SELECT team_innings, batsman_name, runs AS highest_runs
FROM team_top_batsman
WHERE rn = 1;

方案说明 #

1. CTE子查询：先给每个球员按所属队伍分组，计算其在队内的得分排名。
2. 窗口函数：PARTITION BY team_innings确保分组范围是单支队伍，ORDER BY runs DESC让得分最高的球员排在第一位。
3. 筛选结果：只保留排名为1的记录，即每队得分最高的球员。

备选方案：子查询关联 #

如果数据库不支持窗口函数，可以用子查询先找出每队的最高得分，再关联原表获取对应球员信息：

SELECT 
    bs.team_innings,
    bs.batsman_name,
    bs.runs AS highest_runs
FROM batting_summary bs
INNER JOIN (
    SELECT team_innings, MAX(runs) AS max_runs
    FROM batting_summary
    -- 同样可添加赛事筛选条件
    GROUP BY team_innings
) team_max ON bs.team_innings = team_max.team_innings AND bs.runs = team_max.max_runs;

注：该方案会返回队内所有同分最高的球员，若只需一名，需额外处理（比如加DISTINCT或限制返回条数）。

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