Oracle SQL如何生成笛卡尔积的无对称重复唯一子集

现有Oracle数据库表t(a char)，表里存着值"a"、"b"、"c"。需要生成该表笛卡尔积的唯一子集，得满足两个要求：

• 剔除t1.a = t2.a的自身匹配记录
• 把(a,b)和(b,a)这种对称重复的对子当成同一组，只留其中一个，最终结果只要是{(a,b),(a,c),(b,c)}或者{(b,a),(c,a),(c,b)}这类就行

目前已经用下面的语句排除了自身匹配，但还存在对称重复的问题：

select t1.a t1a,t2.a t2a from t t1,t t2 where t1.a<>t2.a;

尝试的其他语句没生效，要求用Oracle原生SQL实现，不能用存储过程或者迭代编程。

方法1：用字符比较（最简洁直接） #

直接通过字符的大小关系筛选，只保留t1.a小于t2.a的组合（要是想留反向的，换成t1.a > t2.a就行）：

SELECT t1.a AS t1a, t2.a AS t2a
FROM t t1, t t2
WHERE t1.a <> t2.a
  AND t1.a < t2.a;

逻辑很简单：对于(x,y)和(y,x)这组对称记录，只会有一组满足x < y（或者x > y），这样就自动去掉了重复的对称对。

方法2：用ROWID（适配字符无法直接比较的场景） #

如果字段不是能直接比较的字符类型，就用每条记录唯一的ROWID来区分，保证每个对称组只留一条：

SELECT t1.a AS t1a, t2.a AS t2a
FROM t t1, t t2
WHERE t1.a <> t2.a
  AND t1.ROWID < t2.ROWID;

原理：每个表记录的ROWID是独一无二的，通过比较ROWID的大小，就能确保每个对称组合只会被选中一次。

方法3：分析函数分组去重（复杂场景更灵活） #

用ROW_NUMBER()函数按对称组分组后取第一条，适合字段类型复杂或者需要自定义排序的场景：

WITH paired_data AS (
    SELECT t1.a AS t1a,
           t2.a AS t2a,
           ROW_NUMBER() OVER (PARTITION BY LEAST(t1.a, t2.a), GREATEST(t1.a, t2.a) ORDER BY t1.a) AS rn
    FROM t t1, t t2
    WHERE t1.a <> t2.a
)
SELECT t1a, t2a
FROM paired_data
WHERE rn = 1;

逻辑：用LEAST和GREATEST把(x,y)和(y,x)归到同一个分组里，然后给每组的记录编序号，只取序号为1的那条，就能实现去重。

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