MySQL5.7查询同weight条目时如何避免双向重复匹配结果

问题原因 #

当前查询逻辑会为每个条目查找相同weight下其他条目的最小名称作为匹配项，当两个条目互为对方的最小匹配项时，就会产生双向重复的配对结果。

修改后的查询语句 #

SELECT * FROM (
    SELECT 
        t1.entryName AS mname, 
        t1.weight AS weight1,
        MIN(t2.entryName) AS wname,
        MIN(t2.weight) AS weight2
    FROM entry t1
    LEFT JOIN entry t2 
        ON t1.weight = t2.weight 
        AND t1.entryName != t2.entryName
    GROUP BY t1.entryID, t1.entryName, t1.weight
) AS tmp
WHERE 
    wname IS NULL
    OR NOT EXISTS (
        SELECT 1 
        FROM entry t3
        WHERE t3.weight = tmp.weight1
          AND t3.entryName = tmp.wname
          AND (
              SELECT MIN(t4.entryName) 
              FROM entry t4 
              WHERE t4.weight = t3.weight 
                AND t4.entryName != t3.entryName
          ) = tmp.mname
          AND tmp.mname > tmp.wname
    )

逻辑说明 #

• 内层查询保留原有匹配逻辑，同时将重复的weight别名改为weight1和weight2避免字段冲突
• 外层过滤仅保留两类行：
  • 未匹配到任何同weight其他条目的行（wname为NULL）
  • 匹配到的条目和当前条目不是双向互为最小匹配，或者虽为双向匹配但当前mname的字典序更小，避免重复保留配对
• 该逻辑完全兼容MySQL 5.7版本，不需要窗口函数支持，执行结果和期望的目标结果完全一致

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