select a.类别||'+'||b.类别 from table a,table b where a.类别<>b.类别
connect by语句不是很精通,不知道能否实现,而且也不够灵活 所以还是用存储过程吧
SQL> WITH t1 AS 2 ( 3 SELECT 'a' AS col FROM dual UNION 4 SELECT 'b' FROM dual UNION 5 SELECT 'c' FROM dual UNION 6 SELECT 'd' FROM dual 7 ), 8 t2 AS 9 ( 10 SELECT 'a' AS col FROM dual UNION 11 SELECT 'b' FROM dual UNION 12 SELECT 'c' FROM dual UNION 13 SELECT 'd' FROM dual 14 ) 15 SELECT t1.col,t2.col,t1.col||'+'||t2.col 16 FROM t1,t2 17 WHERE t1.col < t2.col 18 ORDER BY t1.col,t2.col 19 ;
COL COL T1.COL||'+'||T2.COL --- --- ------------------- a b a+b a c a+c a d a+d b c b+c b d b+d c d c+d
所以还是用存储过程吧
2 (
3 SELECT 'a' AS col FROM dual UNION
4 SELECT 'b' FROM dual UNION
5 SELECT 'c' FROM dual UNION
6 SELECT 'd' FROM dual
7 ),
8 t2 AS
9 (
10 SELECT 'a' AS col FROM dual UNION
11 SELECT 'b' FROM dual UNION
12 SELECT 'c' FROM dual UNION
13 SELECT 'd' FROM dual
14 )
15 SELECT t1.col,t2.col,t1.col||'+'||t2.col
16 FROM t1,t2
17 WHERE t1.col < t2.col
18 ORDER BY t1.col,t2.col
19 ;
COL COL T1.COL||'+'||T2.COL
--- --- -------------------
a b a+b
a c a+c
a d a+d
b c b+c
b d b+d
c d c+d
6 rows selected
b
c
a+b
a+c
a+b+c
b+c