トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-246 優先順位を持った多対1の外部結合
SQLパズル
TEST_A TEST_B
ID Key1 Key2 ID NO Key1 Key2
-- ---- ---- -- -- ---- ----
1 A 1 1 1 A 1
2 B null 1 2 A 2
3 C null 2 3 B null
4 D 1 3 4 C 1
5 D 1 4 5 D 1
6 E 1 5 6 D 1
7 F 1 6 7 E 1
8 F 2 6 8 E 1
7 1 F null
8 2 F 1
TEST_AのID・Key1・Key2をキーにTEST_BからNOを取り出す。
ただし、必ずID・Key1・Key2が一致するとは限らず、
その場合はKey1・Key2を条件にしNOを取り出す。
TEST_AのKey2がNullの場合、
TEST_BのID・Key1が一致し、Key2がNullのデータからNOを取り出す。
一致しない場合はKey1が一致し、Key2がNullのデータからNOを取り出す。
それでも一致しない場合はNOをNullとする。
複数一致する場合は、NOの昇順に1件のみ取り出す。
出力結果
ID NO KEY1 KEY2
-- ---- ---- ------
1 1 A 1
2 3 B null
3 null C null
4 5 D 1
5 6 D 1
6 7 E 1
7 2 F 1
8 null F 2
データ作成スクリプト
create table TEST_A(
ID char primary key,
Key1 char,
Key2 char);
insert into TEST_A
select '1','A','1' from dual union
select '2','B',null from dual union
select '3','C',null from dual union
select '4','D','1' from dual union
select '5','D','1' from dual union
select '6','E','1' from dual union
select '7','F','1' from dual union
select '8','F','2' from dual;
create table TEST_B(
ID char,
NO number(1),
Key1 char,
Key2 char,
primary key(ID,NO));
insert into TEST_B
select '1',1,'A','1' from dual union
select '1',2,'A','2' from dual union
select '2',3,'B',null from dual union
select '3',4,'C','1' from dual union
select '4',5,'D','1' from dual union
select '5',6,'D','1' from dual union
select '6',7,'E','1' from dual union
select '6',8,'E','1' from dual union
select '7',1,'F',null from dual union
select '8',2,'F','1' from dual;
commit;
SQL
col ID for a8
col No for 9999
col Key1 for a8
col Key2 for a8
--■■■Trueを結合条件とした外部結合を使う方法■■■
select ID,No,Key1,Key2
from (select ID,Key1,Key2,priority,
Row_Number() over(partition by Row_ID order by priority,No) as Rn,
case priority when 999 then to_number(null) else No end as No
from (select a.ID,b.No,a.Key1,a.Key2,a.RowID as Row_ID,
case when a.ID = b.ID and a.Key1 = b.Key1 and a.Key2 = b.key2 then 1
when a.Key1 = b.Key1 and a.Key2 = b.key2 then 2
when a.Key2 is null and a.ID = b.ID
and a.Key1 = b.Key1 and b.Key2 is null then 3
when a.Key2 is null and a.Key1 = b.Key1 and b.Key2 is null then 4
else 999 end as priority
from TEST_A a Left join TEST_B b
on 1=1))
where Rn = 1
order by ID;
--■■■クロスジョインを使う方法1■■■
select ID,No,Key1,Key2
from (select ID,Key1,Key2,priority,
Row_Number() over(partition by Row_ID order by priority,No) as Rn,
case priority when 999 then to_number(null) else No end as No
from (select a.ID,b.No,a.Key1,a.Key2,a.RowID as Row_ID,
case when a.ID = b.ID and a.Key1 = b.Key1 and a.Key2 = b.key2 then 1
when a.Key1 = b.Key1 and a.Key2 = b.key2 then 2
when a.Key2 is null and a.ID = b.ID
and a.Key1 = b.Key1 and b.Key2 is null then 3
when a.Key2 is null and a.Key1 = b.Key1 and b.Key2 is null then 4
else 999 end as priority
from TEST_A a,TEST_B b))
where Rn = 1
order by ID;
--■■■クロスジョインを使う方法2(decode関数を使って簡略化)■■■
select ID,No,Key1,Key2
from (select ID,Key1,Key2,
Row_Number() over(partition by Row_ID order by priority,No) as Rn,
case priority when 999 then to_number(null) else No end as No
from (select a.ID,b.No,a.Key1,a.Key2,a.RowID as Row_ID,
case when a.ID = b.ID and a.Key1 = b.Key1
and decode(a.Key2,b.key2,1)=1 then 1
when a.Key1 = b.Key1
and decode(a.Key2,b.key2,1)=1 then 2
else 999 end as priority
from TEST_A a,TEST_B b))
where Rn = 1
order by ID;
--■■■case式のwhen句の論理和で外部結合する方法1■■■
select ID,No,Key1,Key2
from (select a.ID,b.No,a.Key1,a.Key2,
Row_Number() over(partition by a.ID
order by case when a.ID = b.ID and a.Key1 = b.Key1
and decode(a.Key2,b.key2,1)=1 then 1
when a.Key1 = b.Key1
and decode(a.Key2,b.key2,1)=1 then 2 end,No) as Rn
from TEST_A a Left Join TEST_B b
on a.Key1 = b.Key1 and decode(a.Key2,b.key2,1)=1)
where Rn = 1
order by ID;
--■■■case式のwhen句の論理和で外部結合する方法2■■■
select distinct a.ID,
First_Value(No)
over(partition by a.ID
order by case when a.ID = b.ID and a.Key1 = b.Key1
and decode(a.Key2,b.key2,1)=1 then 1
when a.Key1 = b.Key1
and decode(a.Key2,b.key2,1)=1 then 2 end,No) as No,
a.Key1,a.Key2
from TEST_A a Left Join TEST_B b
on a.Key1 = b.Key1 and decode(a.Key2,b.key2,1)=1
order by ID;
解説
・クロスジョイン
・trueを条件とした外部結合
上記の結合結果に違いが発生するのは、結合対象の集合が空集合の時のみです。
空集合を考慮する必要があるかによって、使い分けるといいでしょう。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
case式のwhen句の論理和で外部結合する方法では、
ブール代数を下記のように使って、論理和を同値変形しています。
a.ID = b.ID をX
a.Key1 = b.Key1 をY
decode(a.key2,b.key2,1)=1 をZ
とおくと
case式の全てのwhen句の論理和は、
X*Y*Z + Y*Z
とおけるので、
X*Y*Z + Y*Z
= (Y*Z)*(X+1) = Y*Z
A+A*B = A
と同じ考え方です。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
7-77 優先順位のあるデータ取得