5-11 最大値のデータと外部結合

SQLパズル

TabA
Key1   Data1
----   -----
   1       2
   2       2
   3       1

TabB
Key1  Key2  Data2
----  ----  -----
   1     4      p
   1     8      f
   3     3      q

TabA.Key1=TabB.Key1の関係ですが、必ずしも結合出来るデータがあるとは限りません。
TabBに結合できるデータが複数ある時は、Key2が一番大きい値のデータが取得する。

出力結果
Key1   Key2   Data1   Data2
----   ----   -----   -----
   1      8       2       f
   2   NULL       2    null
   3      3       1       q

データ作成スクリプト

create table TabA(
Key1  char(1),
Data1 char(1));

create table TabB(
Key1  char(1),
Key2  char(1),
Data2 char(1));

insert into TabA values('1','2');
insert into TabA values('2','2');
insert into TabA values('3','1');
insert into TabB values('1','4','p');
insert into TabB values('1','8','f');
insert into TabB values('3','3','q');
commit;

SQL

--■■■select句で相関サブクエリを使用する方法■■■
select Key1,
(select max(b.Key2) from TabB b where b.Key1=a.Key1) Key2,
Data1,
(select b.Data2 from TabB b where b.Key1=a.Key1
                              and b.Key2=(select max(c.Key2) from TabB c
                                           where c.Key1=b.Key1)) as Data2
from TabA a;

--■■■max関数を使用する方法■■■
select a.Key1,b.Key2,a.Data1,b.Data2
from TabA a,
(select Key1,Key2,Data2 from TabB bb
  where bb.Key2 = (select max(cc.Key2) from TabB cc
                    where cc.Key1=bb.Key1)) b
where a.Key1=b.Key1(+);

--■■■分析関数を使用する方法1■■■
select a.Key1,b.Key2,a.Data1,b.Data2
from TabA a,
(select Key1,Key2,Data2 from (
    select Key1,Key2,Data2,max(Key2) over(partition by Key1) as max_Key2 from TabB bb)
 where Key2 = max_Key2) b
where a.Key1=b.Key1(+);

--■■■分析関数を使用する方法2■■■
select a.Key1,b.Key2,a.Data1,b.Data2
from TabA a
Left Join (select Key1,Key2,Data2,max(Key2) over(partition by Key1) as max_Key2 from TabB) b
  on (b.Key2 = b.max_Key2 and a.Key1=b.Key1);

解説

1対1の結合なので、select句での相関サブクエリで取得できます。

---