现有一张表,表中的数据如下:
group_no large_group
2 1
3 1
4 1
1 2
4 3
5 3
这表里面有三个大组,分别是:1、2、3,然后1里面有2、3、4这几个小组,2里面有1这个小组,3里面有4和5这两个小组。
有没有什么方法得出这张表里面所有小组的组合,如:2 1 4,这三个小组分别属于不同的大组,类似的还有3 1 4等...
group_no large_group
2 1
3 1
4 1
1 2
4 3
5 3
这表里面有三个大组,分别是:1、2、3,然后1里面有2、3、4这几个小组,2里面有1这个小组,3里面有4和5这两个小组。
有没有什么方法得出这张表里面所有小组的组合,如:2 1 4,这三个小组分别属于不同的大组,类似的还有3 1 4等...
with tbl as
(
select 2 as group_no, 1 as large_group from dual
union all
select 3 as group_no, 1 as large_group from dual
union all
select 4 as group_no, 1 as large_group from dual
union all
select 1 as group_no, 2 as large_group from dual
union all
select 4 as group_no, 3 as large_group from dual
union all
select 5 as group_no, 3 as large_group from dual
)
select substr(sys_connect_by_path(group_no, ','), 2) as group_no
from tbl
where connect_by_isleaf = 1
start with large_group = 1
connect by large_group = prior large_group + 1;
a.group_no,b.group_no,c.group_no
from table1 as a,table1 as b,table1 as c
where a.group_no<>b.group_no and b.group_no<>c.group_no and a.group_no<>c.group_no
and a.large_group=1 and b.large_group=2 and c.large_group=3
這樣?
--用二楼的数据,改了一下:--SQL:
with tbl as
(
select 2 as group_no, 1 as large_group from dual
union all
select 3 as group_no, 1 as large_group from dual
union all
select 4 as group_no, 1 as large_group from dual
union all
select 1 as group_no, 2 as large_group from dual
union all
select 4 as group_no, 3 as large_group from dual
union all
select 5 as group_no, 3 as large_group from dual
)
select group_no
from
(
select substr(sys_connect_by_path(group_no, ','), 2) as group_no
from tbl
start with large_group = 1
connect by large_group = prior large_group + 1
)
where length(group_no)-length(replace(group_no,',',''))>1
--result:
2,1,4
2,1,5
3,1,4
3,1,5
4,1,4
4,1,5
from
(
select substr(sys_connect_by_path(group_no, ','), 2) as group_no ,level lvl
from tbl
start with large_group = 1
connect by large_group = prior large_group + 1
)
where lvl=3