7-77 優先順位のあるデータ取得

SQLパズル

TranTable    MasterTable
PKey         PKey  Fld
----         ----  ----
AA-1         AA    AAAA
AA-2         AA-3  BBBB
AA-3         BB    CCCC
BB-1         BB-1  DDDD
BB-2         BB-2  EEEE
CC-1         CC    FFFF
CC-2

TranTableのPKeyに紐づく、MasterTableのFldを取得する。
TranTableのPKeyに紐づくデータが存在しない場合は、
MasterTableのPKeyと先頭2文字が一致するデータのFldを取得する。

出力結果
KEY1  FLD1
----  ----
AA-1  AAAA
AA-2  AAAA
AA-3  BBBB
BB-1  DDDD
BB-2  EEEE
CC-1  FFFF
CC-2  FFFF

データ作成スクリプト

create table TranTable(PKey varchar2(4) primary key);

create table MasterTable(
PKey varchar2(4) primary key,
Fld  varchar2(4));

insert all
into TranTable   values('AA-1')
into TranTable   values('AA-2')
into TranTable   values('AA-3')
into TranTable   values('BB-1')
into TranTable   values('BB-2')
into TranTable   values('CC-1')
into TranTable   values('CC-2')
into MasterTable values('AA'  ,'AAAA')
into MasterTable values('AA-3','BBBB')
into MasterTable values('BB'  ,'CCCC')
into MasterTable values('BB-1','DDDD')
into MasterTable values('BB-2','EEEE')
into MasterTable values('CC'  ,'FFFF')
select 1 from dual;
commit;

SQL

--■■■First_Value関数を使う方法■■■
select PKey,
(select distinct First_Value(b.Fld)
                 over(order by case when b.PKey = a.PKey then 0
                                    when b.PKey = substr(a.PKey,1,2) then 1 end)
   from MasterTable b) as Fld
  from TranTable a
 order by PKey;

--■■■2回外部結合させる方法■■■
select a.PKey,nvl(b.Fld,c.Fld) as Fld
  from TranTable a
  Left Join MasterTable b on (b.PKey = a.PKey)
  Left Join MasterTable c on (c.PKey = substr(a.PKey,1,2))
 order by PKey;

解説

2回外部結合させる方法が分かりやすいでしょう。
10-246 優先順位を持った多対1の外部結合

---