使用GROUP BY与GROUP_CONCAT计算带罚分的赛事排名求助

问题背景 #

现有results表，结构与数据如下：

需要按competitor_id分组，生成包含罚分（+1.0）的选手分组排名，结果格式要求如下：

补充说明：M代表选手未参加该赛事，需在分组结果中体现所有赛事（包括未参赛的），未参赛赛事对应排名替换为+1.0罚分，选手至少参加一场赛事。

用户尝试的代码：

CREATE TABLE results (
  result_id INTEGER PRIMARY KEY, 
  competition_id INTEGER,
  competitor_id INTEGER,
  competitor_ranking 
);

INSERT INTO results(competition_id, competitor_id, competitor_ranking) VALUES 
  (1, 1, 0.1), (1, 2, 0.4), (1, 3, 0.2), (1, 4, 0.3),
  (2, 1, 0.4), (2, 2, 0.1), (2, 3, 0.2), (2, 5, 0.3),
  (3, 3, 0.1), (3, 4, 0.4), (3, 5, 0.2), (3, 6, 0.3)
;

SELECT
  competitor_id,
  group_concat(coalesce(competition_id, NULL), '; ') AS competitions,
  group_concat(coalesce(competitor_ranking, NULL), '; ') AS rankings,
  group_concat(coalesce(NULLIF(competitor_ranking, NULL), '+1.0'), '; ') AS ranking_with_penalties
FROM results
GROUP BY competitor_id;

解决方案 #

核心思路是先生成所有选手与所有赛事的笛卡尔积，补全未参赛的赛事记录，再关联原表数据，最后按选手分组拼接结果。

完整SQL代码 #

-- 获取所有赛事ID
WITH all_competitions AS (
    SELECT DISTINCT competition_id FROM results
),
-- 获取所有选手ID
all_competitors AS (
    SELECT DISTINCT competitor_id FROM results
),
-- 生成选手与赛事的全组合，覆盖未参赛情况
competitor_competition_pairs AS (
    SELECT 
        ac.competitor_id,
        comp.competition_id
    FROM all_competitors ac
    CROSS JOIN all_competitions comp
),
-- 关联原表，匹配参赛记录，未参赛记录的ranking为NULL
joined_data AS (
    SELECT 
        ccp.competitor_id,
        ccp.competition_id,
        r.competitor_ranking
    FROM competitor_competition_pairs ccp
    LEFT JOIN results r 
        ON ccp.competitor_id = r.competitor_id 
        AND ccp.competition_id = r.competition_id
)
-- 分组拼接各列结果
SELECT
    competitor_id,
    GROUP_CONCAT(CASE WHEN competitor_ranking IS NOT NULL THEN competition_id ELSE 'M' END, '; ') AS competitions,
    GROUP_CONCAT(competitor_ranking, '; ') AS rankings,
    GROUP_CONCAT(CASE WHEN competitor_ranking IS NOT NULL THEN competitor_ranking ELSE '+1.0' END, '; ') AS ranking_with_penalties
FROM joined_data
GROUP BY competitor_id
ORDER BY competitor_id;

代码说明 #

1. all_competitions：提取所有存在的赛事ID，确保覆盖所有需要展示的赛事。
2. all_competitors：提取所有参赛选手ID。
3. competitor_competition_pairs：通过笛卡尔积生成每个选手与每个赛事的组合，补全选手未参赛的赛事记录。
4. joined_data：左连接原表，将选手参赛的记录匹配，未参赛记录的competitor_ranking字段为NULL。
5. 最终查询：使用GROUP_CONCAT拼接时，通过CASE分支处理未参赛场景：
   • competitions列：参赛显示赛事ID，未参赛显示M
   • rankings列：仅拼接存在的排名（NULL会被GROUP_CONCAT自动忽略，符合需求）
   • ranking_with_penalties列：参赛显示排名，未参赛显示+1.0

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