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

概要

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

基本的に解法１と同じ考え方ですね。
違うのはCASE式使ったぐらいですね。

要件

・重役と役職持ちを取得したい。
・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 １人２役　その３
SELECT person,
        CASE WHEN COUNT(*) = 1
        THEN MAX(role)
        ELSE 'Both' END AS role
    FROM Roles 
    WHERE role IN ('D','O')
    GROUP BY person
	Having Count(*) = 1
	UNION
	SELECT R2.person, 'B'
	FROM Roles AS R2
	WHERE R2.role IN ('D','O')
	GROUP BY R2.person
	HAVING COUNT(*) = 2
	;   
-- UNION前のSQL部分を確認する
SELECT person,
        CASE WHEN COUNT(*) = 1
        THEN MAX(role)
        ELSE 'Both' END AS role
    FROM Roles 
    WHERE role IN ('D','O')
    GROUP BY person
	Having Count(*) = 1
;

-- UNION後のSQL部分を確認する
	SELECT R2.person, 'B'
	FROM Roles AS R2
	WHERE R2.role IN ('D','O')
	GROUP BY R2.person
	HAVING COUNT(*) = 2
	; 

参考

SQLパズル　p152

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

達人に学ぶSQL徹底指南書第２版 p18 CASE式の中で集約関数を使う。

github 差分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/4e0496903f2e6c73bec47cac52b223d54fdcad1c