refirio.org

Memo

メモ > 技術 > データベース: MySQL > 達人に学ぶSQL徹底指南書第2版
■達人に学ぶSQL徹底指南書 第2版

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ（ミック）｜翔泳社の本
https://www.shoeisha.co.jp/book/detail/9784798157825■CASEでラベルを読み替え

単純CASE式、検索CASE式とも、結果は同じ
単純CASE式の方が簡潔に書くことができるが、できることも限られている

CREATE TABLE members(
    name VARCHAR(32) PRIMARY KEY,
    sex  INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '名簿';

INSERT INTO members VALUES('山田太郎', 1);
INSERT INTO members VALUES('山田花子', 2);
INSERT INTO members VALUES('鈴木一郎', 1);
INSERT INTO members VALUES('佐藤次郎', 1);
INSERT INTO members VALUES('田中三郎', 1);
INSERT INTO members VALUES('岡田智子', 2);

-- 単純CASE式で読み替え
SELECT
    name,
    CASE sex
        WHEN '1' THEN '男'
        WHEN '2' THEN '女'
        ELSE 'その他'
        END AS sex
FROM
    members
;

-- 検索CASE式で読み替え
SELECT
    name,
    CASE
        WHEN sex = '1' THEN '男'
        WHEN sex = '2' THEN '女'
        ELSE 'その他'
        END AS sex
FROM
    members
;
■既存のコード体系を新しい体系に変換して集計CREATE TABLE prefs(
    pref_name  VARCHAR(32) PRIMARY KEY,
    population INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '都道府県';

INSERT INTO prefs VALUES('徳島', 100);
INSERT INTO prefs VALUES('香川', 200);
INSERT INTO prefs VALUES('愛媛', 150);
INSERT INTO prefs VALUES('高知', 200);
INSERT INTO prefs VALUES('福岡', 300);
INSERT INTO prefs VALUES('佐賀', 100);
INSERT INTO prefs VALUES('長崎', 200);
INSERT INTO prefs VALUES('東京', 400);
INSERT INTO prefs VALUES('群馬', 50);

-- 県名を地方名に再分類する
SELECT
    CASE pref_name
        WHEN '徳島' THEN '四国'
        WHEN '香川' THEN '四国'
        WHEN '愛媛' THEN '四国'
        WHEN '高知' THEN '四国'
        WHEN '福岡' THEN '九州'
        WHEN '佐賀' THEN '九州'
        WHEN '長崎' THEN '九州'
        ELSE 'その他'
        END AS district,
    SUM(population)
FROM
    prefs
GROUP BY
    district
;

-- 人口階級ごとに都道府県を分類する
SELECT
    CASE
        WHEN population <  100                      THEN 'A'
        WHEN population >= 100 AND population < 200 THEN 'B'
        WHEN population >= 200 AND population < 300 THEN 'C'
        WHEN population >= 300                      THEN 'D'
        ELSE NULL
        END AS population_class,
    COUNT(*) AS cnt
FROM
    prefs
GROUP BY
    population_class
;
■異なる条件の集計を1つのSQLで行うCREATE TABLE prefs(
    pref_name  VARCHAR(32),
    sex        CHAR(1) NOT NULL,
    population INTEGER NOT NULL,
    PRIMARY KEY(pref_name, sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '都道府県';

INSERT INTO prefs VALUES('徳島', '1', 60);
INSERT INTO prefs VALUES('徳島', '2', 40);
INSERT INTO prefs VALUES('香川', '1', 100);
INSERT INTO prefs VALUES('香川', '2', 100);
INSERT INTO prefs VALUES('愛媛', '1', 100);
INSERT INTO prefs VALUES('愛媛', '2', 50);
INSERT INTO prefs VALUES('高知', '1', 100);
INSERT INTO prefs VALUES('高知', '2', 100);
INSERT INTO prefs VALUES('福岡', '1', 100);
INSERT INTO prefs VALUES('福岡', '2', 200);
INSERT INTO prefs VALUES('佐賀', '1', 20);
INSERT INTO prefs VALUES('佐賀', '2', 80);
INSERT INTO prefs VALUES('長崎', '1', 125);
INSERT INTO prefs VALUES('長崎', '2', 125);
INSERT INTO prefs VALUES('東京', '1', 250);
INSERT INTO prefs VALUES('東京', '2', 150);

-- 男性の人口
SELECT
    pref_name,
    population
FROM
    prefs
WHERE
    sex = '1'
;

-- 女性の人口
SELECT
    pref_name,
    population
FROM
    prefs
WHERE
    sex = '2'
;


以下のようにすれば、1つのSQLで処理できる

-- 男性の人口と女性の人口
SELECT
    pref_name,
    -- 男性の人口
    SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_male,
    -- 女性の人口
    SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_female
FROM
    prefs
GROUP BY
    pref_name
;
■条件を分岐させたUPDATE

以下の条件で更新をかけるとする

1. 現在の給料が30万円以上の社員は、10%の減給とする
2. 現在の給料が25万円以上28万円未満の社員は、20%の昇給とする

UPDATE文を2回実行すると、1回目のSQLによって値が変わるため、意図した更新ができない
CASEを使って同時に実行することで、意図した結果を得られるうえに、1回の実行で済むのでパフォーマンスもいい

CREATE TABLE personnel(
    name  VARCHAR(32) PRIMARY KEY,
    salary INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '従業員';

INSERT INTO personnel VALUES('相田', 300000);
INSERT INTO personnel VALUES('神崎', 270000);
INSERT INTO personnel VALUES('木村', 220000);
INSERT INTO personnel VALUES('斎藤', 290000);

-- 条件1
UPDATE
    personnel
SET
    salary = salary * 0.9
WHERE
    salary >= 300000
;

-- 条件2
UPDATE
    personnel
SET
    salary = salary * 1.2
WHERE
    salary >= 250000 AND salary < 280000
;


以下のようにすれば、1つのSQLで処理できる

-- 条件1と条件2を同時に実行する
UPDATE
    personnel
SET
    salary =
        CASE
            WHEN salary >= 300000                     THEN salary * 0.9
            WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
            ELSE salary END
;
■テーブル同士のマッチング

CASE式の大きな利点は、式を評価できること
つまりCASE式の中でBETWEEN、LIKE、<、>といった便利な述語群を利用できる
中でもINとEXISTSはサブクエリを式に取れるため、非常に強力な表現力を持つ

CREATE TABLE course_master(
    course_id   INTEGER PRIMARY KEY,
    course_name VARCHAR(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'コース内容';

INSERT INTO course_master VALUES(1, '経理入門');
INSERT INTO course_master VALUES(2, '財務知識');
INSERT INTO course_master VALUES(3, '簿記検定');
INSERT INTO course_master VALUES(4, '税理士');

CREATE TABLE course_schedule(
    month       INTEGER,
    course_id   INTEGER,
    PRIMARY KEY(month, course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'コース予定';

INSERT INTO course_schedule VALUES(201806, 1);
INSERT INTO course_schedule VALUES(201806, 3);
INSERT INTO course_schedule VALUES(201806, 4);
INSERT INTO course_schedule VALUES(201807, 4);
INSERT INTO course_schedule VALUES(201808, 2);
INSERT INTO course_schedule VALUES(201808, 4);


以下のように処理できる
結果は同じだが、パフォーマンスはEXISTS方式の方がいい

-- INでテーブル同士のマッチング
SELECT
    course_name,
        CASE
            WHEN course_id IN (
                SELECT course_id FROM course_schedule WHERE month = 201806
            ) THEN '○'
            ELSE '×' END AS "6月",
        CASE
            WHEN course_id IN (
                SELECT course_id FROM course_schedule WHERE month = 201807
            ) THEN '○'
            ELSE '×' END AS "7月",
        CASE
            WHEN course_id IN (
                SELECT course_id FROM course_schedule WHERE month = 201808
            ) THEN '○'
            ELSE '×' END AS "8月"
FROM
    course_master
;

-- EXISTSでテーブル同士のマッチング
SELECT
    cm.course_name,
        CASE
            WHEN EXISTS (
                SELECT course_id FROM course_schedule cs WHERE month = 201806 AND cs.course_id = cm.course_id
            ) THEN '○'
            ELSE '×' END AS "6月",
        CASE
            WHEN EXISTS (
                SELECT course_id FROM course_schedule cs WHERE month = 201807 AND cs.course_id = cm.course_id
            ) THEN '○'
            ELSE '×' END AS "7月",
        CASE
            WHEN EXISTS (
                SELECT course_id FROM course_schedule cs WHERE month = 201808 AND cs.course_id = cm.course_id
            ) THEN '○'
            ELSE '×' END AS "8月"
FROM
    course_master cm
;
■CASE式の中で集約関数を使う

学生番号と、その学生の所属クラブを管理するテーブルがあるとする
学生は複数のクラブに所属している（100, 200）場合もあれば、1つのクラブにしか所属していない場合もある
複数のクラブに所属している学生については、主なクラブを表すフラグ列に「Y」が入り、それ以外には「N」が入る
1つのクラブに専念している学生の場合は「N」が入る

このテーブルから、以下の条件でデータを取得する

1. 1つだけのクラブに所属している学生については、そのクラブIDを取得する
2. 複数のクラブに所属している学生については、主なクラブのIDを取得する

CREATE TABLE student_club(
    std_id        INTEGER,
    club_id       INTEGER,
    club_name     VARCHAR(32),
    main_club_flg CHAR(1),
    PRIMARY KEY (std_id, club_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生の所属クラブ';

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

-- 条件1
SELECT
    std_id, MAX(club_id) AS main_club
FROM
    student_club
GROUP BY
    std_id
HAVING
    COUNT(*) = 1
;

-- 条件2
SELECT
    std_id, club_id AS main_club
FROM
    student_club
WHERE
    main_club_flg = 'Y'
;


以下のようにすれば、1つのSQLで処理できる

SELECT
    std_id,
        CASE
            WHEN COUNT(*) = 1     -- 1つだけのクラブに所属している学生の場合
                THEN MAX(club_id)
            ELSE MAX(             -- 複数のクラブに所属している学生の場合
                CASE
                    WHEN main_club_flg = 'Y' THEN club_id
                    ELSE NULL
                    END
            ) END AS main_club
FROM
    student_club
GROUP BY
    std_id
;
■自己結合で検索

同じテーブルを結合して、高度な検索を行うことができる

CREATE TABLE address(
    name      VARCHAR(32),
    family_id INTEGER,
    address   VARCHAR(32),
    PRIMARY   KEY(name, family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '住所録';

INSERT INTO address VALUES('前田 義明', '100', '東京都港区虎ノ門3-2-29');
INSERT INTO address VALUES('前田 由美', '100', '東京都港区虎ノ門3-2-92');
INSERT INTO address VALUES('加藤 茶',   '200', '東京都新宿区西新宿2-8-1');
INSERT INTO address VALUES('加藤 勝',   '200', '東京都新宿区西新宿2-8-1');
INSERT INTO address VALUES('ホームズ',  '300', 'ベーカー街221B');
INSERT INTO address VALUES('ワトソン',  '400', 'ベーカー街221B');

-- 同じ家族で、住所が違うレコードを検索する（間違って登録されたデータを探す）
SELECT
    DISTINCT A1.name, A1.address 
FROM
    address A1 INNER JOIN address A2 
ON
    A1.family_id = A2.family_id AND A1.address <> A2.address
;
CREATE TABLE products(
    name  VARCHAR(16) NOT NULL,
    price INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '商品';

INSERT INTO products VALUES('りんご', 50);
INSERT INTO products VALUES('みかん', 100);
INSERT INTO products VALUES('ぶどう', 50);
INSERT INTO products VALUES('スイカ', 80);
INSERT INTO products VALUES('レモン', 30);
INSERT INTO products VALUES('いちご', 100);
INSERT INTO products VALUES('バナナ', 100);

-- 同じ値段で、商品名が違うレコードを検索する
SELECT
    DISTINCT P1.name, P1.price 
FROM
    products P1 INNER JOIN products P2 
ON
    P1.price = P2.price 
AND
    P1.name <> P2.name
ORDER BY
    P1.price;
Memo

Advertisement
