sql语句如何实现排列组合?

显示全部楼层 · 2014-2-8 06:00:03

现在我有一个users表,如下:
NAME VALUEID
---------- ---------- ----------
甲 a
1
乙 b
2
丙 c
3
丁 d
4
现在,需要对他们进行两两组合,比如说ab和ba就是一样的,现在需要利用select语句查询出所有abcd的两两组合的情况,最后的结果应该是6个,ab,ac,ad,bc,bd,cd,请问利用sql语句如何实现以上的查询?

千问 · 2014-2-8 06:00:03

[php]
select prior value, value
from users
where level = 2
connect by value > prior value
and level b.rowid
and a.value 最初由 shiyisaniya 发布
[B]select a.value||b.value result
from test_j a,test_j b
where a.rowidb.rowid
and a.value
不错!

千问 · 2014-2-8 06:00:03

base data
SQL> create table users (name char(2),value char(1),id number);

Table created

SQL> insert into users values('甲','a',1);

1 row inserted

SQL> insert into users values('乙','b',2);

1 row inserted

SQL> insert into users values('丙','c',3);

1 row inserted

SQL> insert into users values('丁','d',4);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from users;

NAME VALUE ID
---- ----- ----------
甲 a
1
乙 b
2
丙 c
3
丁 d
4

千问 · 2014-2-8 06:00:03

SQL> select o.p as "排列" from
2(select replace (sys_connect_by_path( value, ',' ) , ',' ) p
3from users connect by nocycle value != prior value) o
4where length(o.p) =2;

排列
--------------------------------------------------------------------------------
ab
ac
ad
ba
bc
bd
ca
cb
cd
da
db
dc

12 rows selected
SQL> select o.p as "排列" from
2(select replace (sys_connect_by_path( value, ',' ) , ',' ) p
3from users connect by nocycle value != prior value) o
4where length(o.p) =3;

排列
--------------------------------------------------------------------------------
abc
abd
acb
acd
adb
adc
bac
bad
bca
bcd
bda
bdc
cab
cad
cba
cbd
cda
cdb
dab
dac

排列
--------------------------------------------------------------------------------
dba
dbc
dca
dcb

24 rows selected

SQL>
SQL> select o.p as "排列" from
2(select replace (sys_connect_by_path( value, ',' ) , ',' ) p
3from users connect by nocycle value != prior value) o
4where length(o.p) =4;

排列
--------------------------------------------------------------------------------
abcd
abdc
acbd
acdb
adbc
adcb
bacd
badc
bcad
bcda
bdac
bdca
cabd
cadb
cbad
cbda
cdab
cdba
dabc
dacb

排列
--------------------------------------------------------------------------------
dbac
dbca
dcab
dcba

24 rows selected
[ 本帖最后由 XQKA 于 2010-3-25 14:24 编辑 ]

千问 · 2014-2-8 06:00:03

SQL> select replace (a.combo, '#') as "组合"
2from
3(select id,sys_connect_by_path (value, '#') || '#' combo
4
from (select 1 as id,value,1 as ctrl from users)
5
connect by prior id = id and value > prior value ) a,
6
(select 1 as id,value,1 as ctrl from users) b
7 where b.id = a.id and instr (a.combo, '#' || b.value || '#') > 0
8group by a.id, a.combo
9having sum (b.ctrl) = 2;

组合
--------------------------------------------------------------------------------
ab
bd
ac
cd
bc
ad

6 rows selected

SQL>
SQL> select replace (a.combo, '#') as "组合"
2from
3(select id,sys_connect_by_path (value, '#') || '#' combo
4
from (select 1 as id,value,1 as ctrl from users)
5
connect by prior id = id and value > prior value ) a,
6
(select 1 as id,value,1 as ctrl from users) b
7 where b.id = a.id and instr (a.combo, '#' || b.value || '#') > 0
8group by a.id, a.combo
9having sum (b.ctrl) = 3;

组合
--------------------------------------------------------------------------------
acd
abc
abd
bcd

SQL>
SQL> select replace (a.combo, '#') as "组合"
2from
3(select id,sys_connect_by_path (value, '#') || '#' combo
4
from (select 1 as id,value,1 as ctrl from users)
5
connect by prior id = id and value > prior value ) a,
6
(select 1 as id,value,1 as ctrl from users) b
7 where b.id = a.id and instr (a.combo, '#' || b.value || '#') > 0
8group by a.id, a.combo
9having sum (b.ctrl) = 4;

组合
--------------------------------------------------------------------------------
abcd
asktom.com

千问 · 2014-2-8 06:00:03

还是有点缺陷。
即，value有相同值的时候会失真。
SQL> update users set value='a' where id=4;

1 row updated

SQL> commit;

Commit complete

SQL> select * from users;

NAME VALUE ID
---- ----- ----------
甲 a
1
乙 b
2
丙 c
3
丁 a
4

SQL>
SQL> select o.p as "排列" from
2(select replace (sys_connect_by_path( value, ',' ) , ',' ) p
3from users connect by nocycle value != prior value) o
4where length(o.p) =2;

排列
--------------------------------------------------------------------------------
ab
ac
ba
bc
ba
ca
cb
ca
ab
ac

10 rows selected

SQL>
SQL> select replace (a.combo, '#') as "组合"
2from
3(select id,sys_connect_by_path (value, '#') || '#' combo
4
from (select 1 as id,value,1 as ctrl from users)
5
connect by prior id = id and value > prior value ) a,
6
(select 1 as id,value,1 as ctrl from users) b
7 where b.id = a.id and instr (a.combo, '#' || b.value || '#') > 0
8group by a.id, a.combo
9having sum (b.ctrl) = 2;

组合
--------------------------------------------------------------------------------
bc

千问 · 2014-2-8 06:00:03

依旧老问题