MySQL田径数据报表：多表关联获取选手最佳成绩及日期

报错原因分析 #

你遇到的"Subquery returns more than one row"错误，是因为子查询没有和主查询的选手做关联，直接对所有metric_type_id=26的记录按player_year_id分组，返回了多条结果，但主查询的每一行只能对应一个值，导致不匹配。

修复思路与完整SQL语句 #

要生成包含各项目最佳成绩及对应日期的报表，我们需要：

1. 区分跑步项目（取最小成绩）和田赛项目（取最大成绩）
2. 同时获取最佳成绩对应的赛事日期
3. 将各项目的结果横向拼接成报表格式

以下是基于窗口函数实现的完整SQL（假设player_year_metrics表通过event_id关联events表的id字段，player_year_metric_types表的type_name字段存储项目名称如"100 Meter"）：

WITH player_best_metrics AS (
    SELECT 
        p.id AS player_id,
        p.guid AS `Player GUID`,
        p.name AS `Player Full Name`,
        pm.type_name AS metric_type,
        pym.value AS metric_value,
        e.event_date AS metric_date,
        -- 按项目类型排序：跑步项目按成绩升序（取最小），田赛按成绩降序（取最大）
        ROW_NUMBER() OVER (
            PARTITION BY p.id, pm.type_name 
            ORDER BY 
                CASE 
                    WHEN pm.type_name IN ('100 Meter', '200 Meter') THEN pym.value 
                    ELSE -pym.value 
                END ASC
        ) AS rn
    FROM players p
    JOIN player_years py ON p.id = py.player_id
    JOIN player_year_metrics pym ON py.id = pym.player_year_id
    JOIN player_year_metric_types pm ON pym.metric_type_id = pm.id
    JOIN events e ON pym.event_id = e.id
)
SELECT 
    `Player GUID`,
    `Player Full Name`,
    -- 提取各项目的最佳成绩和对应日期
    MAX(CASE WHEN metric_type = '100 Meter' THEN metric_value END) AS `100 Meter Best`,
    MAX(CASE WHEN metric_type = '100 Meter' THEN metric_date END) AS `100 Meter Best Date`,
    MAX(CASE WHEN metric_type = '200 Meter' THEN metric_value END) AS `200 Meter Best`,
    MAX(CASE WHEN metric_type = '200 Meter' THEN metric_date END) AS `200 Meter Best Date`,
    MAX(CASE WHEN metric_type = 'Shotput' THEN metric_value END) AS `Shotput Best`,
    MAX(CASE WHEN metric_type = 'Shotput' THEN metric_date END) AS `Shotput Best Date`,
    MAX(CASE WHEN metric_type = 'Discus' THEN metric_value END) AS `Discus Best`,
    MAX(CASE WHEN metric_type = 'Discus' THEN metric_date END) AS `Discus Best Date`,
    MAX(CASE WHEN metric_type = 'Long Jump' THEN metric_value END) AS `Long Jump Best`,
    MAX(CASE WHEN metric_type = 'Long Jump' THEN metric_date END) AS `Long Jump Best Date`
FROM player_best_metrics
WHERE rn = 1 -- 仅保留每个选手每个项目的最佳记录
GROUP BY `Player GUID`, `Player Full Name`;

关键说明 #

• 如果你的项目类型不是用名称而是用metric_type_id区分，把CASE语句里的pm.type_name替换成对应的pm.id即可，比如WHEN pm.id = 26 THEN ...（对应100 Meter）。
• 窗口函数ROW_NUMBER()会为每个选手的每个项目生成排序号，rn=1就是该选手该项目的最佳成绩记录。
• 最后用CASE语句将纵向的项目记录转换为横向的报表列。

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