Oracle怎么组合,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;

组合

--------------------------------------------------------------------------------

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