10-297 重複組み合わせ

SQLパズル

comb_dupテーブル
 i
--
 1
 1
 1
 1
 2
 2
 2
 2
 3
 3
 4

同じ物を2個以上選ぶことを許可した組み合わせの
結果と件数を求める

【結果例】
1個選ぶ場合、下記の4件
1
2
3
4

2個選ぶ場合、下記の9件
1,1
1,2
1,3
1,4
2,2
2,3
2,4
3,3
3,4

3個選ぶ場合、下記の15件
1,1,1
1,1,2
1,1,3
1,1,4
1,2,2
1,2,3
1,2,4
1,3,3
1,3,4
2,2,2
2,2,3
2,2,4
2,3,3
2,3,4
3,3,4

データ作成スクリプト

create table comb_dup(i number);

insert into comb_dup values(1);
insert into comb_dup values(1);
insert into comb_dup values(1);
insert into comb_dup values(1);
insert into comb_dup values(2);
insert into comb_dup values(2);
insert into comb_dup values(2);
insert into comb_dup values(3);
insert into comb_dup values(3);
insert into comb_dup values(4);
commit;

SQL

create or replace function print_comb_dup(LEV in number) return varchar2 IS
    件数 pls_Integer := 0;
    WillOut varchar2(4000);
begin
    for rec_Work in
    (select distinct substr(sys_connect_by_path(to_char(i),','),2) as 組み合わせ
       from (select i,Row_Number() over (order by i) as rank from comb_dup)
      where Level = LEV
     connect by prior rank < rank) Loop
         WillOut := WillOut || '■' || rec_Work.組み合わせ;
         件数 := 件数 + 1;
    end Loop;
    return substr(WillOut,2) || '件数は' || to_char(件数) || '件';
end;
/
sho err

select RowNum as 引数,
print_comb_dup(RowNum) as 結果
from all_catalog
where RowNum <= 10;

解説

Row_Number関数で順位をつけてから、
階層問い合わせを使って、
組み合わせを列挙してます

---