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

yksky

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

nyfor

[php]
select prior value, value
  from users
where level = 2
connect by value > prior value
       and level <= 2;

PRIORVALUE VALUE
---------- -----
a          b
a          c
a          d
b          c
b          d
c          d

6 rows selected
......
[/php]

shiyisaniya

select a.value||b.value result
from test_j a,test_j b
where a.rowid<>b.rowid
and a.value <b.value
order by result
-------------------------------------
result

ab
ac
ad
bc
bd
cd

yksky

谢谢

nyfor

最初由 shiyisaniya 发布
[B]select a.value||b.value result
from test_j a,test_j b
where a.rowid<>b.rowid
and a.value <b.value
order by result
-------------------------------------
result

ab
ac
ad
bc
bd
cd [/B]

不错!

XQKA

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

XQKA

SQL> select o.p as "排列" from
  2  (select replace (sys_connect_by_path( value, ',' ) , ',' ) p
  3  from users connect by nocycle value != prior value) o
  4  where 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
  3  from users connect by nocycle value != prior value) o
  4  where 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
  3  from users connect by nocycle value != prior value) o
  4  where 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 编辑 ]

XQKA

SQL> select replace (a.combo, '#') as "组合"
  2  from
  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
  8  group by a.id, a.combo
  9  having sum (b.ctrl) = 2;

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

6 rows selected

SQL>
SQL> select replace (a.combo, '#') as "组合"
  2  from
  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
  8  group by a.id, a.combo
  9  having sum (b.ctrl) = 3;

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

SQL>
SQL> select replace (a.combo, '#') as "组合"
  2  from
  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
  8  group by a.id, a.combo
  9  having sum (b.ctrl) = 4;

组合
--------------------------------------------------------------------------------
abcd

asktom.com

XQKA

还是有点缺陷。
即，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
  3  from users connect by nocycle value != prior value) o
  4  where length(o.p) =2;

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

10 rows selected

SQL>
SQL> select replace (a.combo, '#') as "组合"
  2  from
  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
  8    group by a.id, a.combo
  9    having sum (b.ctrl) = 2;

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

zhanghong

依旧老问题