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

8-49 結合数を列値に持つ内部結合

SQLパズル

TableA           TableB
Name  ID  Num    ID
----  --  ---    --
AAAA   1    2     1
BBBB   7    3     3
CCCC  12    1     6
                  7
                  8
                 12

TableAとTableBを
TableA.ID <= TableB.ID を条件として内部結合する。
ただし、TableA.Numが結合行数の上限となり、
結合対象となるTableB.IDの昇順でTableA.Numの行数しか内部結合できない。

出力結果
Name  ID  NUM  BID
----  --  ---  ---
AAAA   1    2    1
AAAA   1    2    3
BBBB   7    3    7
BBBB   7    3    8
BBBB   7    3   12
CCCC  12    1   12

こちらを参考にさせていただきました


データ作成スクリプト

create table TableA(Name,ID,Num) as
select 'AAAA', 1,2 from dual union
select 'BBBB', 7,3 from dual union
select 'CCCC',12,1 from dual;

create table TableB(ID) as
select * from table(sys.odciNumberList(1,3,6,7,8,12));


SQL

--■■■分析関数を使用■■■
select Name,ID,Num,BID
from (select a.Name,a.ID,a.Num,b.ID as BID,
      rank() over(partition by a.Name order by b.ID) as rn
        from TableA a,TableB b
       where a.ID <= b.ID)
 where Num >= rn;

--■■■相関サブクエリを使用■■■
select a.Name,a.ID,a.Num,b.ID as BID
  from TableA a,TableB b
 where a.ID <= b.ID
   and (select count(*)
          from TableB c
         where c.ID between a.ID and b.ID) <= a.Num;


解説

分析関数を使えば、楽ですが、
使えないとなると複雑になりますね。

8-5 移動平均を求める