使用SQL创建Pivot Table：查询各赛季出场次数最多的球员

原代码的问题 #

你写的SELECT Player_ID, COUNT(*)FROM match GROUP BY Player_ID HAVING COUNT(*) max存在几个核心问题：

1. 缺失赛季维度：仅按Player_ID分组，统计的是球员跨赛季的总出场数，无法得到分赛季的结果
2. 语法错误：HAVING COUNT(*) max不符合SQL语法，MAX()是聚合函数，不能直接这样使用
3. 未关联球员表：无法获取球员的名字信息，仅能得到Player_ID
4. 逻辑偏差：没有针对每个赛季筛选最大出场次数球员的逻辑

分步实现方案 #

1. 统计每个球员分赛季的出场次数

先关联match和person表，计算每个球员在各赛季的出场数：

SELECT 
    m.Season,
    p.Player_ID,
    p.Player,
    COUNT(m.Match_ID) AS appearances
FROM match m
JOIN person p ON m.Player_ID = p.Player_ID
GROUP BY m.Season, p.Player_ID, p.Player

2. 提取每个赛季的最大出场次数

基于上述统计结果，获取每个赛季的最高出场数：

SELECT Season, MAX(appearances) AS max_appearances
FROM (
    SELECT 
        m.Season,
        m.Player_ID,
        COUNT(m.Match_ID) AS appearances
    FROM match m
    GROUP BY m.Season, m.Player_ID
) AS season_player_counts
GROUP BY Season

3. 获取每个赛季出场最多的球员

关联前两步的结果，同时支持处理并列情况（同一赛季多个球员出场次数相同且均为最多）：

SELECT 
    sc.Season,
    p.Player,
    sc.appearances
FROM (
    SELECT 
        m.Season,
        m.Player_ID,
        COUNT(m.Match_ID) AS appearances
    FROM match m
    GROUP BY m.Season, m.Player_ID
) AS sc
JOIN (
    SELECT Season, MAX(appearances) AS max_appearances
    FROM (
        SELECT 
            m.Season,
            m.Player_ID,
            COUNT(m.Match_ID) AS appearances
        FROM match m
        GROUP BY m.Season, m.Player_ID
    ) AS sub
    GROUP BY Season
) AS sm ON sc.Season = sm.Season AND sc.appearances = sm.max_appearances
JOIN person p ON sc.Player_ID = p.Player_ID
ORDER BY sc.Season

4. 转换为透视表（Pivot Table）

如果需要将赛季作为列展示（如Season1、Season2作为表头），这里提供通用的CASE写法（适配SQLite、MySQL等多数数据库）：

SELECT 
    '出场最多球员' AS 指标,
    MAX(CASE WHEN Season = 'Season1' THEN Player END) AS Season1,
    MAX(CASE WHEN Season = 'Season2' THEN Player END) AS Season2,
    MAX(CASE WHEN Season = 'Season3' THEN Player END) AS Season3
    -- 根据实际赛季值，继续添加更多CASE语句
FROM (
    SELECT 
        sc.Season,
        p.Player,
        sc.appearances
    FROM (
        SELECT 
            m.Season,
            m.Player_ID,
            COUNT(m.Match_ID) AS appearances
        FROM match m
        GROUP BY m.Season, m.Player_ID
    ) AS sc
    JOIN (
        SELECT Season, MAX(appearances) AS max_appearances
        FROM (
            SELECT 
                m.Season,
                m.Player_ID,
                COUNT(m.Match_ID) AS appearances
        FROM match m
        GROUP BY m.Season, m.Player_ID
        ) AS sub
        GROUP BY Season
    ) AS sm ON sc.Season = sm.Season AND sc.appearances = sm.max_appearances
    JOIN person p ON sc.Player_ID = p.Player_ID
) AS top_players

若使用支持PIVOT语法的数据库（如SQL Server），可采用更简洁的写法：

SELECT *
FROM (
    SELECT 
        sc.Season,
        p.Player
    FROM (
        SELECT 
            m.Season,
            m.Player_ID,
            COUNT(m.Match_ID) AS appearances
        FROM match m
        GROUP BY m.Season, m.Player_ID
    ) AS sc
    JOIN (
        SELECT Season, MAX(appearances) AS max_appearances
        FROM (
            SELECT 
                m.Season,
                m.Player_ID,
                COUNT(m.Match_ID) AS appearances
            FROM match m
            GROUP BY m.Season, m.Player_ID
        ) AS sub
        GROUP BY Season
    ) AS sm ON sc.Season = sm.Season AND sc.appearances = sm.max_appearances
    JOIN person p ON sc.Player_ID = p.Player_ID
) AS top_players
PIVOT (
    MAX(Player)
    FOR Season IN ([Season1], [Season2], [Season3])
) AS pivot_table

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