用SQL得到全组合-mysql教程-PHP中文网

用SQL得到全组合

php中文网

发布： 2016-06-07 14:56:53

原创

1712人浏览过

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

现有一个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;

登录后复制

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;

登录后复制

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;

登录后复制

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;

登录后复制