在ORACLE中使用SQL語句實現排列組合

SQL> select * from users;
 
NAME VALUE         ID
---- ----- ----------
t1   a              1
t2   b              2
t3   c              3
t4   d              4

 

--實現value列的值的兩兩組合

 

SQL> select REPLACE(sys_connect_by_path (value, '#'),'#') combo
  2  from users
  3  where level = 2
  4  connect by prior value < value and level <= 2;
 
COMBO
--------------------------------------------------------------------------------
ab
ac
ad
bc
bd
cd

6 rows selected

 

--實現value列的值的兩兩排列

 

SQL> select REPLACE(sys_connect_by_path (value, '#'),'#') combo
  2  from users
  3  where level = 2
  4  connect by nocycle prior value != value and level <= 2;
 
COMBO
--------------------------------------------------------------------------------
ab
ac
ad
ba
bc
bd
ca
cb
cd
da
db
dc
 
12 rows selected

 

 

測試指令碼如下

create table users (name char(2),value char(1),id number);
insert into users values('t1','a',1);
insert into users values('t2','b',2);
insert into users values('t3','c',3);
insert into users values('t4','d',4);
commit;