Oracle PL/SQL遍历未定义的组数

Question

我正在为下面的数据集编写一个oracle /SQL过程。

列2-列2-第3列ID-1 Group1项目11 ID-1 Group1项目12 ID-1 Group1项目13 ID-1 Group2项目21 ID-1 Group2项目22 ID-2 Group3项目31 ID-2 Group3项目32 ID-2 Group4项目41 ID-2 Group5项目51 ID-2 Group5项目52

根据输入id，我将得到少数组，即在每个组中有几个项目轮流。我需要一个连接如下的结果集：

输入: ID-1 OutPut在单列中：

项目11 -项目21项目11 -项目22项目12 -项目21项目12 -项目22项目13 -项目21项目13 -项目22

输入: ID-2 OutPut在单列中：

项目31 -项目41 -项目51项目31 -项目41 -项目52项目32 -项目41 -项目51项目32 -项目41 -项目52

输出应该包含每个组中属于输入ID的所有项的组合。注意，ID下的数字组不是固定的，组中的项数也不是固定的。

Answer 1

使用dense_rank()对组编号，然后在分层查询中使用此列在connect by子句中进行通信：

select ltrim(sys_connect_by_path(column3, ' - '), ' - ') as list 
  from (select dense_rank() over (order by column2) rnk, column1, column2, column3
          from yourtable where column1 = 'ID-1')   
  where connect_by_isleaf = 1
  connect by prior rnk + 1 = rnk 
  start with rnk = 1

测试数据和输出：

create table yourtable (Column1 varchar2(10), Column2 varchar2(10), Column3 varchar2(10));
insert into yourtable values( 'ID-1', 'Group1', 'Item 11');
insert into yourtable values( 'ID-1', 'Group1', 'Item 12');
insert into yourtable values( 'ID-1', 'Group1', 'Item 13');
insert into yourtable values( 'ID-1', 'Group2', 'Item 21');
insert into yourtable values( 'ID-1', 'Group2', 'Item 22');
insert into yourtable values( 'ID-2', 'Group3', 'Item 31');
insert into yourtable values( 'ID-2', 'Group3', 'Item 32');
insert into yourtable values( 'ID-2', 'Group4', 'Item 41');
insert into yourtable values( 'ID-2', 'Group5', 'Item 51');
insert into yourtable values( 'ID-2', 'Group5', 'Item 52');

LIST
--------------------
Item 11 - Item 21
Item 11 - Item 22
Item 12 - Item 21
Item 12 - Item 22
Item 13 - Item 21
Item 13 - Item 22    

Answer 2

select substr(B.perm,4,length(B.perm)) from 
(select level l,sys_connect_by_path(A.Column3, ' - ') as perm , sys_connect_by_path(A.Column2, ' - ') ttt
from (select t.* from yourTable t where t.Column1 = 'ID-2')A -- ID-2 is your input
connect by level <= 3 -- 3 is your distinct group count for your input
order by level)B
where B.l = 3 and instr(B.ttt , 'Group3 - Group4 - Group5') > 0 -- 3 is your distinct group count for your input , *** Group3 - Group4 - Group5 is your merged data according to your input
order by substr(B.perm,4,length(B.perm))asc

我测试它，如果你设置值正确的话，这两个ID都能工作。