SQL得到全组合代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL得到全组合
作者:DDT / 发布于2013/1/8/ 674
现有一个users表,如下: 
NAME       VALUE      ID 
---------- ---------- ---------- 
甲         a          1 
乙         b          2 
丙         c          3 
丁         d          4 
现在,需要对他们进行两两组合,比如说ab和ba就是一样的,现在需要利用select语句查询出所有abcd的两两组合的情况,最后的结果应该是6个,ab,ac,ad,bc,bd,cd。


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;
试试其它关键字
　全组合　
同语言下
可能有用的
DDT贡献的其它代码(160)