-- 笛卡尔积? SQL> SELECT * 2 FROM TABLE T1, 3 TABLE T2 4 WHERE T1.FLAG <> T2.FLAG;FLAG FLAG ---- ---- A B A C A D B A B C B D C A C B C D D A D B D C12 rows selected
你这个不可以吧。。 第一行 A B 出现了 那么后面 就不应该在出现 A B 或者 B A ,如果出现不就重复了吗?我要求的结果是这样的 A B A C A D B C B D C D
-- 早说, 不过这只能适合1位数字符的情况: SQL> SELECT T1FLAG, T2FLAG 2 FROM (SELECT T1.FLAG T1FLAG, 3 T2.FLAG T2FLAG, 4 ROW_NUMBER() OVER(PARTITION BY ASCII(T1.FLAG) * ASCII(T2.FLAG) ORDER BY ASCII(T1.FLAG) * ASCII(T2.FLAG)) RN 5 FROM TABLE T1, 6 TABLE T2 7 WHERE T1.FLAG <> T2.FLAG) TT 8 WHERE RN = 1;T1FLAG T2FLAG ------ ------ A B A C A D B C B D C D6 rows selected
SQL> SELECT *
2 FROM TABLE T1,
3 TABLE T2
4 WHERE T1.FLAG <> T2.FLAG;FLAG FLAG
---- ----
A B
A C
A D
B A
B C
B D
C A
C B
C D
D A
D B
D C12 rows selected
A B
A C
A D
B C
B D
C D
SQL> SELECT T1FLAG, T2FLAG
2 FROM (SELECT T1.FLAG T1FLAG,
3 T2.FLAG T2FLAG,
4 ROW_NUMBER() OVER(PARTITION BY ASCII(T1.FLAG) * ASCII(T2.FLAG) ORDER BY ASCII(T1.FLAG) * ASCII(T2.FLAG)) RN
5 FROM TABLE T1,
6 TABLE T2
7 WHERE T1.FLAG <> T2.FLAG) TT
8 WHERE RN = 1;T1FLAG T2FLAG
------ ------
A B
A C
A D
B C
B D
C D6 rows selected