SQLパズル　p57 14 電話とFAX　その２

Last updated at 2024-11-12Posted at 2024-11-12

概要

SQLパズル　p57 14 電話とFAX　その２の解法メモです。
題材は外部結合（OUTER JOIN）を使った行→列変換です。

要件

・分類のカラムが有り、値にはhomかfaxの２つの値を取る
・番号のカラムが有り、値には番号が入る。分類のカラムがhomなら電話番号、faxならfaxの番号

実現したいこととは、１つのレコードに家の電話番号とfax番号を表示させるようにしたい。
3列目が電話番号、４列目がfax番号

（行→列変換）

SQL

DDL

drop Table if exists personnel cascade;

CREATE table personnel
(emp_id integer primary key,
first_name char(20) not null,
last_name char(20) not null
);
drop table if exists phones cascade;
create table phones
(emp_id integer not null,
phone_type char(3) not null check(phone_type IN('hom','fax')),
phone_nbr char(12) not null,
primary key(emp_id,phone_type)
)
;
INSERT INTO Personnel VALUES(1, '山田', '太郎');
INSERT INTO Personnel VALUES(2, '上野', '二郎');
INSERT INTO Personnel VALUES(3, '高田', '三郎');
INSERT INTO Personnel VALUES(4, '松岡', '四郎');

INSERT INTO Phones VALUES(1, 'hom', 1111);
INSERT INTO Phones VALUES(1, 'fax', 2222);
INSERT INTO Phones VALUES(2, 'hom', 3333);
INSERT INTO Phones VALUES(3, 'fax', 4444);

答えのSQL

-- 答え
SELECT E1.last_name,E1.first_name,H1.phone_nbr AS Home,
       F1.phone_nbr AS FAX
    FROM (Personnel AS E1 LEFT OUTER JOIN Phones AS H1 -- 結合１回目
           ON E1.emp_id = H1.emp_id
           AND H1.phone_type = 'hom'
         )
         LEFT OUTER JOIN Phones AS F1	-- 結合２回目
           ON E1.emp_id = F1.emp_id
           AND F1.phone_type = 'fax'
;


-- 結合１回目
SELECT *
    FROM (Personnel AS E1 LEFT OUTER JOIN Phones AS H1
           ON E1.emp_id = H1.emp_id
           AND H1.phone_type = 'hom'
         )
;
-- 結合２回目
SELECT *
    FROM (Personnel AS E1 LEFT OUTER JOIN Phones AS H1
           ON E1.emp_id = H1.emp_id
           AND H1.phone_type = 'hom'
         )
         LEFT OUTER JOIN Phones AS F1
           ON E1.emp_id = F1.emp_id
           AND F1.phone_type = 'fax'
;