SQL:删除自联接中的重复项

Question

我有下表(称为 t1):

| id | Name    |
| 1  | Charlie |
| 2  | Bob     |
| 3  | Alice   |

我想将表与自身匹配(自联接)，但只选择尚未出现的组合。到目前为止，我有以下几点:

select * from t1 a, t1 b
where a.id != b.id

这给了我这个结果:

| a.id | a.Name  | b.id | b.Name  |
| 2    | Bob     | 1    | Charlie | 
| 3    | Alice   | 1    | Charlie | 
| 1    | Charlie | 2    | Bob     | 
| 3    | Alice   | 2    | Bob     | 
| 1    | Charlie | 3    | Alice   | 
| 2    | Bob     | 3    | Alice   | 

我只希望一个 id 从表 a 中出现一次，从表 b 中出现一次。期望的结果是:

| a.id | a.Name  | b.id | b.Name  |
| 2    | Bob     | 1    | Charlie | 
| 3    | Alice   | 2    | Bob     | 
| 1    | Charlie | 3    | Alice   |

但我对如何保证这一点感到困惑。

我正在使用 SQL Server 2017。

这是我的测试的一个 fiddle : DEMO

PS:我查过 this问题，但在我自己的示例中，我不清楚使用“小于”作为比较运算符的解决方案的概念。

编辑:没有关于选择哪一对的规则；这些对可能是 (2,3), (3,1), (1,2) 而不是我上面介绍的那些，因为我感兴趣的唯一规则是 表 a 和表 b 中的每个 id 只有一次 , 和 a.id != b.id .

编辑 2:没有逻辑可以匹配它们，请考虑作为这个可能的前提:
我正在为爱丽丝、鲍勃和查理做媒，就好像他们在 secret 交换礼物一样。他们只能给一个人送礼物，只能收一件礼物，不能给自己送礼物。 (我认为这允许可扩展性)

Answer 1

这是一个使用 ROW_NUMBER 的选项用不同的名字错开每个名字的技巧:

WITH cte AS (
    SELECT id, Name, ROW_NUMBER() OVER (ORDER BY id) rn
    FROM t1
)

SELECT
    t1.Name,
    t2.Name
FROM cte t1
INNER JOIN cte t2
    ON (t1.rn % (SELECT COUNT(*) FROM cte)) + 1 = t2.rn;

Demo

逻辑是将行号 1 与 2、2 与 3、3 与 1 匹配(我们使用模数在边缘情况下环绕)。这确保在给定的列中不会出现任何名称多次。