sql - 识别表中逻辑重复的行

Question

这是我的输入表。

   ID   | Player1 | Player2 | Player3
  ===================================
   101  |   Tom   |  Dick   | Harry
   102  |  Jack   | Martin  | Anna
   103  |  Harry  |  Tom    | Dick

在这里我们看到，对于 ID 101 和 103，玩家本质上是相同的，尽管顺序不同。因此，它们需要被视为重复行。

我如何通过编写查询以某种方式识别重复的行？
返回“ID 101 和 ID 103 是重复的”的东西？？

Answer 1

/*
CREATE  TABLE myTest
    (ID INT,
    Player1 VARCHAR(50),
    Player2 VARCHAR(50),
    Player3 VARCHAR(50));
GO

INSERT INTO myTest
VALUES
    (101, 'Tom', 'Dick', 'Harry'),
    (102, 'Jack', 'Martin', 'Anna'),
    (103, 'Harry', 'Tom', 'Dick');
GO
*/
WITH
    Players AS (
        SELECT
            ID,
            Player1 AS Player
        FROM
            myTest
        UNION ALL
        SELECT
            ID,
            Player2 AS Player
        FROM
            myTest
        UNION ALL
        SELECT
            ID,
            Player3 AS Player
        FROM
            myTest),
    PlayersSorted AS (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Player) AS RowNo
        FROM
            Players)
SELECT DISTINCT
    a.ID
FROM
    PlayersSorted a
JOIN
    PlayersSorted b
        ON a.ID <> b.ID
        AND a.Player = b.Player
        AND a.RowNo = b.RowNo;