编写三列关联无重复SQL查询 实现各地点唯一获奖队伍统计

实现思路 #

这是典型的贪心匹配场景，核心逻辑按优先级排序后逐步选取：

1. 所有参赛记录按分数从高到低全局排序，优先分配高分获奖资格
2. 每次选取当前可用的最高分记录：要求对应队伍未获奖、对应举办地未分配获奖名额
3. 重复选取直到所有举办地都分配到获奖名额为止

参考SQL（支持递归CTE的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH ranked_by_place AS (
    SELECT 
        TEAM_ID,
        PLACE,
        SCORE,
        -- 同场地内按分数降序排名，同分按ENTRY_ID升序避免歧义
        ROW_NUMBER() OVER (PARTITION BY PLACE ORDER BY SCORE DESC, ENTRY_ID ASC) AS place_rank
    FROM competition_entries
),
RECURSIVE award_selection AS (
    -- 第一轮选全局最高分的场地第一名
    SELECT 
        TEAM_ID,
        PLACE,
        SCORE,
        1 AS level,
        CAST(CONCAT(',', TEAM_ID, ',') AS CHAR(1000)) AS used_teams,
        CAST(CONCAT(',', PLACE, ',') AS CHAR(1000)) AS used_places
    FROM ranked_by_place
    WHERE place_rank = 1
    ORDER BY SCORE DESC
    LIMIT 1
    UNION ALL
    -- 每一轮迭代选剩余可用的最高分给未分配场地
    SELECT 
        r.TEAM_ID,
        r.PLACE,
        r.SCORE,
        a.level + 1 AS level,
        CONCAT(a.used_teams, r.TEAM_ID, ',') AS used_teams,
        CONCAT(a.used_places, r.PLACE, ',') AS used_places
    FROM award_selection a
    JOIN ranked_by_place r
        ON a.used_teams NOT LIKE CONCAT('%,', r.TEAM_ID, ',%')
        AND a.used_places NOT LIKE CONCAT('%,', r.PLACE, ',%')
    -- 取当前场地最高可用名次的记录
    WHERE r.place_rank = (
        SELECT MIN(place_rank)
        FROM ranked_by_place r2
        WHERE r2.PLACE = r.PLACE
        AND a.used_teams NOT LIKE CONCAT('%,', r2.TEAM_ID, ',%')
    )
    ORDER BY r.SCORE DESC
    LIMIT 1
)
-- 输出最终获奖名单
SELECT TEAM_ID, PLACE, SCORE FROM award_selection;

运行结果（匹配示例数据） #

适配旧版本数据库方案 #

如果使用不支持递归CTE的数据库（如MySQL 5.x），可以用存储过程实现相同逻辑：

1. 预先统计不同举办地的总数量，作为循环终止条件
2. 初始化已获奖队伍集合、已分配举办地集合、结果集为空
3. 循环N次（N为举办地总数）：
   • 查询当前未获奖队伍、未分配举办地中的分数最高单条记录
   • 将记录写入结果集，同时把对应队伍、举办地标记为已使用
4. 最后输出结果集即可

性能优化建议 #

给SCORE、PLACE、TEAM_ID字段建立联合索引，可大幅提升大表场景下的查询效率，避免全表扫描。 #

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