MySQL多GROUP BY用法：赛扬奖获奖赛季按联赛分组格式化输出

安全阿岳

2026-9-26

赛扬奖获奖统计SQL调整方案

问题原因

你当前的SQL直接将单条获奖记录拼接为「年份(联赛)」的独立条目，没有先按联赛维度合并同联赛的所有获奖年份，因此输出格式不符合要求。

解决思路

采用两层分组聚合实现需求：

内层按「球员+联赛」维度分组，将同一联赛下的所有获奖年份用逗号拼接，末尾带上联赛名称，生成类似2021, 2020 (18+)的单联赛结果
外层按「球员」维度分组，将所有单联赛结果用分号拼接，同时统计总获奖次数

调整后完整SQL

SELECT 
    PLAYER_NAME,
    SUM(league_win_count) AS TotalWins,
    GROUP_CONCAT(league_season_str ORDER BY LEAGUE_ID ASC SEPARATOR '; ') AS Seasons
FROM (
    SELECT 
        PLAYER_NAME,
        L.LEAGUE_ID,
        COUNT(*) AS league_win_count,
        CONCAT(
            GROUP_CONCAT(S.SEASON_YEAR ORDER BY S.SEASON_YEAR DESC SEPARATOR ', '),
            ' (', L.LEAGUE_NAME, ')'
        ) AS league_season_str
    FROM CY_YOUNG
    JOIN SEASONS S ON S.SEASON_ID = CY_YOUNG.SEASON_ID
    JOIN LEAGUES L ON L.LEAGUE_ID = S.LEAGUE_ID
    WHERE CY_YOUNG.PLACE = 1
    GROUP BY PLAYER_NAME, L.LEAGUE_ID, L.LEAGUE_NAME
) AS player_league_stats
GROUP BY PLAYER_NAME
HAVING TotalWins > 1
ORDER BY TotalWins DESC;