有这样一个需求,原始数据为:
NAME GROUP
n1 null
n2 g1
n3 g1
n4 g2
n5 g2
需求,将名称按组进行排列组合,中间用:分隔,要求结果为
RESULT
n1:n2:n4
n1:n2:n5
n1:n3:n4
n1:n3:n5
也就是,每组最多有两个,每多一组,最后的结果集数量翻倍。求高手指点该怎么做,要用sql实现oracle排列组合
NAME GROUP
n1 null
n2 g1
n3 g1
n4 g2
n5 g2
需求,将名称按组进行排列组合,中间用:分隔,要求结果为
RESULT
n1:n2:n4
n1:n2:n5
n1:n3:n4
n1:n3:n5
也就是,每组最多有两个,每多一组,最后的结果集数量翻倍。求高手指点该怎么做,要用sql实现oracle排列组合
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
select * from (
select case when b ='g2' then (
select g from
(
select g,rownum from (select case when b ='g1' then (select a from bb where b is null)||':'|| a end g from bb) f
where g is not null
) g where rownum=1)||':'|| a end ff from bb
union
select case when b ='g2' then (
select g from
(
select g,rownum dd from (select case when b ='g1' then (select a from bb where b is null)||':'|| a end g from bb) f
where g is not null
) g where dd=2)||':'|| a end ff from bb) ggg where ff is not null