10-34 nvl関数とスカラー問い合わせ

SQLパズル

ATbl
KEY  CHUMON
---  ------
  1    0501
  2    0123
  3    4567

BTbl
KEY  ITEM  GK
---  ----  --
  1   100  50
  1   200  80
  1   300  90
  2   100  10
  2   400  90
  3   200  20

リレーションが1対1もしくは、1対2である2テーブルから
ITEMが100もしくは200のGKを出力する(どちらかしかない場合もある)

出力結果
KEY  CHUMON  ITEM100  ITEM200
---  ------  -------  -------
  1    0501       50       80   100,200両方ある場合
  2    0123       10        0   100しかない場合
  3    4567        0       20   200しかない場合

データ作成スクリプト

create table ATbl(
KEY    number(1),
CHUMON char(4));

insert into ATbl values(1,'0501');
insert into ATbl values(2,'0123');
insert into ATbl values(3,'4567');

create table BTbl(
KEY  number(1),
ITEM number(3),
GK   number(2));

insert into BTbl values(1,100,50);
insert into BTbl values(1,200,80);
insert into BTbl values(1,300,90);
insert into BTbl values(2,100,10);
insert into BTbl values(2,400,90);
insert into BTbl values(3,200,20);
commit;

SQL

--■■■スカラーサブクエリを使う方法■■■
select KEY,CHUMON,
nvl((select b.GK from BTbl b
      where b.KEY=a.KEY
        and b.ITEM=100),0) as ITEM100,
nvl((select b.GK from BTbl b
      where b.KEY=a.KEY
        and b.ITEM=200),0) as ITEM200
from ATbl a;

--■■■外部結合してからグループ化する方法■■■
select a.Key,a.CHUMON,
nvl(max(decode(b.ITEM,100,b.GK)),0) as ITEM100,
nvl(max(decode(b.ITEM,200,b.GK)),0) as ITEM200
  from ATbl a Left Join BTbl b
    on (a.KEY = b.KEY
    and b.ITEM in(100,200))
group by a.Key,a.CHUMON
order by a.Key,a.CHUMON;

解説

スカラー問い合わせは、行を返さない場合にnullとなるので、
nvl関数で、nullを0に変換してます。

---