SQLパズル4 入館証 解法1 メモ

概要

SQLパズル4入館証 のp18 の 解法１の学習メモです。
テーマとしては、自己結合の使い方が肝です。

設問

・社員は現在働いてる職場に応じて、複数の入館証を持つことができる。
・同時に有効な入館証は一つだけ
・有効な入館証は　新しい職場で発行された入館証とする。

SQL

DDL

DROP TABLE IF EXISTS personnel CASCADE;
CREATE TABLE personnel
(
    emp_id integer PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL
);

Insert  Into personnel values
(1,'John'),
(2,'Mary'),
(3,'Bill'),
(4,'Mike')
;


DROP TABLE IF EXISTS badges CASCADE;
CREATE TABLE Badges
(badge_nbr INTEGER NOT NULL PRIMARY KEY,
 emp_id INTEGER NOT NULL,
 issued_date DATE NOT NULL,
 badge_status CHAR(1) NOT NULL
    CHECK (badge_status IN ('A', 'I')));



INSERT INTO Badges VALUES(100, 1, '2007-01-01', 'I');
INSERT INTO Badges VALUES(200, 1, '2007-02-01', 'I');  --社員1番の最新バッジ
INSERT INTO Badges VALUES(300, 2, '2007-03-01', 'I');  --社員2番の最新バッジ
INSERT INTO Badges VALUES(400, 3, '2007-03-01', 'I');  
INSERT INTO Badges VALUES(500, 3, '2007-04-01', 'I');  
INSERT INTO Badges VALUES(600, 3, '2007-05-01', 'I');  --社員3番の最新バッジ
INSERT INTO Badges VALUES(800, 4, '2007-07-01', 'I');  
INSERT INTO Badges VALUES(900, 4, '2007-08-01', 'A');  --社員4番の最新バッジ

答えのSQL

-- Update Badges
--     SET badge_status = 'A'
    SELECT * 
    FROM Badges
    WHERE ('I' = ALL (SELECT badge_status   --イの条件
                        FROM Badges AS B1
                        WHERE Badges.emp_id = B1.emp_id))
    AND (issued_date = (SELECT MAX(issued_date) -- ロの条件
                            FROM Badges AS B2
                        WHERE Badges.emp_id = B2.emp_id));

図

分解して考える

イ の条件

①にemp_idに対して、badge_statusの組み合わせを自己結合を使って求める。
②限定術後のALLを使って、全てのレコードがbadge_status='I'であるemp_idを割り出す。

sql

-- ①組み合わせを求める
SELECT B1.*,B2.*
FROM Badges AS B1, Badges AS B2
WHERE B2.emp_id = B1.emp_id;

-- ②emp_idでB2の全てのbadge_statusが'I'のレコードのみを取得する。
SELECT B2.*
FROM Badges AS B2
WHERE
('I' = ALL (SELECT B2.badge_status
              FROM Badges AS B1
              WHERE B2.emp_id = B1.emp_id));

図

ロ の条件

sql

①にemp_idに対して、issued_dateの組み合わせを自己結合を使って求める。
②限定術後のALLを使って、全てのレコードがbadge_status='I'であるemp_idを割り出す。

-- ①emp_idに対して、issued_dateの組み合わせを求める
SELECT B1.emp_id,B1.issued_date
    FROM Badges AS B2,Badges AS B1
	WHERE B1.emp_id = B2.emp_id;

-- ②　①で求めたissued_dateの組み合わせの内最大値を求める。
SELECT * 
FROM Badges AS B1
WHERE
(B1.issued_date = (SELECT MAX(B2.issued_date)
                        FROM Badges AS B2
                        WHERE B1.emp_id = B2.emp_id));

図

イ　AND ロ

SQL

SELECT * 
FROM Badges
WHERE
('I' = ALL (SELECT badge_status
              FROM Badges AS B1
              WHERE Badges.emp_id = B1.emp_id))
AND
(issued_date = (SELECT MAX(issued_date)
                            FROM Badges AS B2
                        WHERE Badges.emp_id = B2.emp_id));

図

参考

SQLパズル　p18

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p4

達人に学ぶSQL徹底指南書第２版 p44　自己結合の使い方　組み合わせ
達人に学ぶSQL徹底指南書第２版 p53　まとめ参考書籍