如何为表中双向匹配的列值对添加统一pair_id？

表结构与数据 #

我有一个名为tt的表，包含a和b两列，列中存在可重复的值，数据如下：

[a] [b]
1   2
1   3
1   4
2   1
2   3
3   2
4   1
4   5
5   4
5   6

当前已实现的查询 #

我已掌握查询双向匹配值对的SQL语句：

SELECT t1.A, t1.B
    FROM tt AS t1
INNER JOIN tt AS t2
    ON t1.A = t2.B AND t1.B = t2.A

需求 #

需要为这些匹配的双向值对添加新列pair_id，使同一值对的行拥有相同的pair_id，期望效果如下：

[a] [b] [pair_id]
1   2   1
2   1   1
1   4   2
4   1   2
2   3   3
3   2   3
4   5   4
5   4   4

解决方案 #

可以通过统一双向值对的“基准标识”，再生成连续的pair_id，具体实现如下：

通用方案（支持多数数据库） #

利用LEAST和GREATEST函数将双向值对转换为统一的有序组合，再用DENSE_RANK()生成id：

SELECT 
    t1.a, 
    t1.b,
    DENSE_RANK() OVER (ORDER BY LEAST(t1.a, t1.b), GREATEST(t1.a, t1.b)) AS pair_id
FROM tt AS t1
INNER JOIN tt AS t2
    ON t1.a = t2.b AND t1.b = t2.a
ORDER BY pair_id, t1.a;

说明

• LEAST(t1.a, t1.b)取两列中的较小值，GREATEST(t1.a, t1.b)取较大值，这样(1,2)和(2,1)会被统一识别为同一个组合。
• DENSE_RANK()会按统一组合的顺序生成连续的pair_id，确保同一双向对的行id一致。
• 末尾的ORDER BY用于对齐示例中的输出顺序。

兼容旧版数据库方案（如SQL Server） #

如果数据库不支持LEAST/GREATEST，可以用CASE语句替代：

SELECT 
    t1.a, 
    t1.b,
    DENSE_RANK() OVER (ORDER BY 
        CASE WHEN t1.a < t1.b THEN t1.a ELSE t1.b END,
        CASE WHEN t1.a > t1.b THEN t1.a ELSE t1.b END
    ) AS pair_id
FROM tt AS t1
INNER JOIN tt AS t2
    ON t1.a = t2.b AND t1.b = t2.a
ORDER BY pair_id, t1.a;

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