如何查询同球员同年份order重复的异常记录？SQL求助

问题背景 #

现有一张包含player、team、year、order字段的表，规则为：同一球员在同一年份可隶属于多支球队，但同一球员+同一年份下的order值必须唯一。表中player=4、year=2020的两条记录order均为0，属于违反规则的异常数据，需要筛选出所有此类异常记录。

解决方案 #

以下两种SQL语句均可实现需求：

方法1：分组筛选重复组合后关联原表

先通过分组找出存在重复的(player, year, order)组合，再关联原表获取完整的异常记录：

SELECT t.*
FROM your_table t
JOIN (
    SELECT player, year, `order`
    FROM your_table
    GROUP BY player, year, `order`
    HAVING COUNT(*) > 1
) dup 
ON t.player = dup.player 
AND t.year = dup.year 
AND t.`order` = dup.`order`
ORDER BY t.player, t.year, t.`order`;

方法2：窗口函数标记重复记录

使用窗口函数COUNT()直接对player、year、order分组统计数量，过滤出数量大于1的记录：

SELECT player, team, year, `order`
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY player, year, `order`) AS duplicate_count
    FROM your_table
) t
WHERE duplicate_count > 1
ORDER BY player, year, `order`;

注意事项 #

• order是SQL保留关键字，因此需要用反引号（MySQL）或方括号（SQL Server）包裹，避免语法错误。
• 两种方法均能返回所有符合异常条件的记录，小数据量下两种方式效率相近，大数据量下分组关联的方式可能更高效。

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