MySQL查询仅适配特定数据格式，如何识别双向重复用户条目？

原查询仅能识别player1和player2顺序完全一致的重复数据（如表#1），对应的SQL语句如下：

select s.id, t.* 
from users s
join (
    select player1,player2, count(*) as qty
    from users
    group by player1, player2
    having count(*) > 1
) t on s.player1 = t.player1 and s.player2 = t.player2

表#1（原查询可正常返回重复数据）：

但当player1和player2顺序互换时（如表#2），原查询会返回空集，无法识别这类逻辑上的重复：
表#2（原查询失效）：

下面提供几种可行的解决方案：

方案1：用最小/最大值统一分组逻辑（推荐） #

核心是把每组的两个玩家按固定顺序（比如字母排序）统一，让(p1,p2)和(p2,p1)被归为同一组，SQL语句如下：

select s.id, t.min_player, t.max_player, t.qty
from users s
join (
    select 
        least(player1, player2) as min_player,
        greatest(player1, player2) as max_player,
        count(*) as qty
    from users
    group by least(player1, player2), greatest(player1, player2)
    having count(*) > 1
) t on (s.player1 = t.min_player and s.player2 = t.max_player) 
    or (s.player1 = t.max_player and s.player2 = t.min_player)

注：least()和greatest()是MySQL等数据库的函数，SQL Server可改用CASE语句实现类似逻辑，比如CASE WHEN player1 < player2 THEN player1 ELSE player2 END作为min_player。

方案2：基于原查询添加OR逻辑修改 #

如果要在原查询基础上调整，可在分组和关联时加入顺序互换的判断，但这种方式性能稍差：

select s.id, s.player1, s.player2, t.total_qty
from users s
join (
    select 
        player1,
        player2,
        count(*) + coalesce((select count(*) from users u where u.player1 = t_inner.player2 and u.player2 = t_inner.player1), 0) as total_qty
    from users t_inner
    group by player1, player2
    having count(*) + coalesce((select count(*) from users u where u.player1 = t_inner.player2 and u.player2 = t_inner.player1), 0) > 1
) t on (s.player1 = t.player1 and s.player2 = t.player2) 
    or (s.player1 = t.player2 and s.player2 = t.player1)

方案3：用窗口函数直接标记重复记录 #

如果只需筛选出所有属于重复对的记录，窗口函数的写法更简洁：

select 
    id,
    player1,
    player2,
    qty
from (
    select 
        *,
        count(*) over (partition by least(player1, player2), greatest(player1, player2)) as qty
    from users
) temp
where qty > 1

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