SQL指南書 CASE式の中で集約関数を使う（CASE文応用）

SQL 指南書集 目次 - ray88’s diary
■学生の所属クラブ一覧表。（テーブル名：StudentClub）
　・複数クラブに所属している場合はメインに活動しているクラブに「Y」のフラグが立っている
　・以下の条件でクエリを発行する
　　１．１つだけのクラブに所属している学生については、そのクラブIDを取得する
　　２．複数のクラブを掛け持ちしている学生については、主なクラブのIDを取得する

■考察：条件１と２のSQLをそれぞれ発行した場合
　【条件1】
　　1つのクラブしか入っていない学生をあぶりだすため std_idで一旦グループ化する必要があり、
　　グループ化するには集計関数を含める必要があるためMAX関数を使用している（MIN関数でも可）

--条件１：１つのクラブに専念している学生を選択
SELECT std_id,MAX(club_id)AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) = 1;

条件1のSQLの出力結果

【条件2】

--条件2：複数のクラブを掛け持ちしている学生を選択
SELECT std_id,club_id AS main_club
FROM StudentClub
WHERE main_club_flg = 'Y';

条件2のSQLの出力結果

■考察：CASE文を使用して条件１と条件２を１つのSQLで書く
　ポイント：集計関数に対する条件はHAVING句を使って設定するが、CASE式を使用するとSELECT句でも同等の条件分岐が書ける
HAVING句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる

/* CASE 式の中で集約関数を使う(p.23) */
SELECT std_id,
       CASE WHEN COUNT(*) = 1 /* 一つのクラブに専念する学生の場合 */
            THEN MAX(club_id)
            ELSE MAX(CASE WHEN main_club_flg = 'Y'
                          THEN club_id
                          ELSE NULL END)
        END AS main_club
  FROM StudentClub
 GROUP BY std_id;

出力結果

【参考】テーブル作成用SQL

/* CASE式の中で集約関数を使う */
CREATE TABLE StudentClub
(std_id  INTEGER,
 club_id INTEGER,
 club_name VARCHAR(32),
 main_club_flg CHAR(1),
 PRIMARY KEY (std_id, club_id));

INSERT INTO StudentClub VALUES(100, 1, '野球',        'Y');
INSERT INTO StudentClub VALUES(100, 2, '吹奏楽',      'N');
INSERT INTO StudentClub VALUES(200, 2, '吹奏楽',      'N');
INSERT INTO StudentClub VALUES(200, 3, 'バドミントン','Y');
INSERT INTO StudentClub VALUES(200, 4, 'サッカー',    'N');
INSERT INTO StudentClub VALUES(300, 4, 'サッカー',    'N');
INSERT INTO StudentClub VALUES(400, 5, '水泳',        'N');
INSERT INTO StudentClub VALUES(500, 6, '囲碁',        'N');