トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

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関数で順位をつけてから、
階層問い合わせを使って、
組み合わせを列挙してます