SQLパズル 36 １人２役 の 解法１の学習メモ

概要

SQLパズル　36 １人２役 の 解法１の学習メモです。

UNIONとHaving句使ってますね。

要件

・重役と役職持ちを取得したい。
・role='O'と'D'が１つの場合、'O','D'のいずれかを表示
・role='O'と'D'を２つ持っている場合、'B'とする。(BothのB)

SQL

DDL

DROP TABLE IF EXISTS Roles;
CREATE TABLE Roles(
 person VARCHAR(32), 
 role   VARCHAR(32), 
    PRIMARY KEY (person, role));

INSERT INTO Roles VALUES('Smith', 'O');
INSERT INTO Roles VALUES('Smith', 'D');
INSERT INTO Roles VALUES('Jones', 'O');
INSERT INTO Roles VALUES('White', 'D');
INSERT INTO Roles VALUES('Brown', 'X');

答えのSQL

-- 36 １人２役　その１
-- UNION
SELECT R1.person,MIN(R1.role)
FROM Roles AS R1
WHERE R1.role IN ('D','O')
GROUP BY R1.person
HAVING COUNT(DISTINCT R1.role) = 1
UNION
	SELECT R2.person,'B'
		FROM Roles AS R2
	WHERE R2.role IN('D','O')
	GROUP BY R2.person
	HAVING  COUNT(DISTINCT R2.role) = 2
;

-- Having句を外して、部分集合を確認する
-- この時点でrole='D' or 'O'を弾く
SELECT R1.person,MIN(R1.role) as first_role,MAX(R1.role) as second_role,COUNT(DISTINCT R1.role)
FROM Roles AS R1
WHERE R1.role IN ('D','O')
GROUP BY R1.person
;


-- UNION前のSQLを確認
-- roleを１つ持っている　Jones Whiteを取得する
-- distinct要らない？？？
SELECT R1.person,MIN(R1.role),COUNT(DISTINCT R1.role)
FROM Roles AS R1
WHERE R1.role IN ('D','O')
GROUP BY R1.person
HAVING COUNT(DISTINCT R1.role) = 1
;


-- UNION後のSQLを確認
-- roleを２つ持っているsmithを取得する
SELECT R2.person,'B',COUNT(R2.role)
	FROM Roles AS R2
WHERE R2.role IN('D','O')
GROUP BY R2.person
HAVING  COUNT(R2.role) = 2
;

参考

SQLパズル　p152

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

達人に学ぶSQL徹底指南書第２版

github 差分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/335008f9af1ac90d8fb3366ce100c46c280555c3