用SQL得到全组合

[sql]代码库

create table users (name char(2),value char(1),id number);
insert into users values('甲','a',1);
insert into users values('乙','b',2);
insert into users values('丙','c',3);
insert into users values('丁','d',4);
commit;

--全组合
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;

--取2个元素组合
select replace (a.combo, '#') as "组合"
   from
    (select id,sys_connect_by_path (value, '#') || '#' combo
             from (select 1 as id,value,1 as ctrl from users)
             connect by prior id = id and value > prior value ) a,
                    (select 1 as id,value,1 as ctrl from users) b
   where b.id = a.id and instr (a.combo, '#' || b.value || '#') > 0
   group by a.id, a.combo
   having sum (b.ctrl) = 2;

--取3个元素组合
select replace (a.combo, '#') as "组合"
   from
     (select id,sys_connect_by_path (value, '#') || '#' combo
             from (select 1 as id,value,1 as ctrl from users)
             connect by prior id = id and value > prior value ) a,
                    (select 1 as id,value,1 as ctrl from users) b
      where b.id = a.id and instr (a.combo, '#' || b.value || '#') > 0
   group by a.id, a.combo
   having sum (b.ctrl) = 3;

--取4个元素组合
select replace (a.combo, '#') as "组合"
   from
     (select id,sys_connect_by_path (value, '#') || '#' combo
             from (select 1 as id,value,1 as ctrl from users)
             connect by prior id = id and value > prior value ) a,
                    (select 1 as id,value,1 as ctrl from users) b
      where b.id = a.id and instr (a.combo, '#' || b.value || '#') > 0
   group by a.id, a.combo
   having sum (b.ctrl) = 4;