格斗赛事匹配APP的SQL查询需求：按体重差匹配且无重复

我正在开发一款格斗赛事匹配APP，需实现基于体重差值的匹配逻辑：匹配双方体重差需≤35（含体重相等），且匹配结果无重复。以下是cock_entry表的结构与数据、期望输出格式，以及当前编写的SQL查询，请求修正该查询以满足需求。

cock_entry表结构及数据 #

期望输出格式 #

当前SQL查询 #

SELECT A.entry_name AS entry_name1, A.owner_name AS owner_name1, A.c_weight AS weight1,
       B.entry_name AS entry_name2, B.owner_name AS owner_name2, B.c_weight AS weight2
FROM cock_entry A
INNER JOIN cock_entry B
ON A.entry_name > B.entry_name AND ABS(A.c_weight - B.c_weight) <= 35
WHERE NOT EXISTS (
    SELECT 1
    FROM cock_entry C
    WHERE A.entry_name = C.entry_name AND A.c_weight = C.c_weight
      AND B.entry_name = C.owner_name AND B.c_weight = C.c_weight
)

修正后的SQL查询及说明 #

问题分析 #

原SQL存在以下问题：

1. 关联条件错误：用A.entry_name > B.entry_name区分匹配双方，但entry_name是赛事分组（HL/HB），并非玩家标识，应该用c_hdr_id区分PLAYER1和PLAYER2的群体
2. 去重逻辑无效：NOT EXISTS子句的条件逻辑混乱，无法实现无重复匹配的要求
3. 输出字段不符：期望输出的entry_name1/2是玩家名称（owner_name），且需要固定的wing1/2字段

修正后的SQL #

WITH player1_data AS (
    -- 提取PLAYER1的所有参赛数据，按体重排序并编号
    SELECT *, ROW_NUMBER() OVER (ORDER BY c_weight) AS weight_rank
    FROM cock_entry 
    WHERE c_hdr_id = 1
),
player2_data AS (
    -- 提取PLAYER2的所有参赛数据，按体重排序并编号
    SELECT *, ROW_NUMBER() OVER (ORDER BY c_weight) AS weight_rank
    FROM cock_entry 
    WHERE c_hdr_id = 2
)
SELECT 
    p1.owner_name AS entry_name1,
    'A' AS wing1,
    p1.c_weight AS weight1,
    p2.owner_name AS entry_name2,
    'A' AS wing2,
    p2.c_weight AS weight2
FROM player1_data p1
JOIN player2_data p2 
    ON p1.weight_rank = p2.weight_rank  -- 按体重排序后一一匹配
    AND ABS(p1.c_weight - p2.c_weight) <= 35;  -- 体重差符合要求

逻辑说明 #

1. 用CTE分别提取两个玩家的参赛数据，按体重排序并生成排名编号
2. 按排名编号一一匹配双方数据，确保每个参赛个体只匹配一次，避免重复
3. 过滤体重差≤35的组合，同时输出符合期望格式的字段

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