oracle排列组合,在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;