【SQL】CASE式を使ったSELECTとUPDATE

CASE式の構文

「単純CASE式」と「検索CASE式」の2つあります。

-- 単純CASE式
CASE column_name
WHEN 1 THEN '一'
WHEN 2 THEN '二'
ELSE 'その他' END

これはcolumn_nameの値が1なら「一」へ、2なら「二」へ、それ以外なら「その他」へ読み替えるという意味です。

-- 検索CASE式
CASE WHEN column_name = 0 THEN 'ゼロ'
            WHEN column_name > 0 AND column_name < 100 THEN '1~99'
            ELSE '100以上' END

これはcolumn_nameの値が0なら「ゼロ」、0より大きく100より小さければ「1~99」、その他なら「100以上」へ読み替えるという意味です。

サンプルデータの作成

learn_sqlデータベースに次の2つのテーブルを作成します。

• 都道府県の人口情報（nameカラムとpopulationカラム）をもつテーブルprefectures
• 従業員の給与情報（nameカラムとsalaryカラム）をもつテーブルemployees

データ作成SQL

-- DATABASE作成とDATABASE切り替え
CREATE DATABASE learn_sql;
use learn_sql;

-- prefecturesテーブル（都道府県）を作成、データINSERT
CREATE TABLE prefectures (name VARCHAR(32) PRIMARY KEY, population INTEGER NOT NULL);
INSERT INTO prefectures (name, population) VALUES
  ('広島', 280),
  ('福岡', 510),
  ('熊本', 175),
  ('岡山', 190),
  ('兵庫', 550),
  ('大阪', 880),
  ('北海道', 530);

-- employeesテーブルを作成、データINSERT
CREATE TABLE employees (name VARCHAR(32) PRIMARY KEY, salary INTEGER NOT NULL);
INSERT INTO employees (name, salary) VALUES
  ('山田太郎', 300000),
  ('佐藤次郎', 270000),
  ('鈴木花子', 220000),
  ('高橋知子', 290000);

以下のようなデータが作成されていればOK

mysql> SELECT * FROM prefectures;
+-----------+------------+
| name      | population |
+-----------+------------+
| 兵庫      |        550 |
| 北海道    |        530 |
| 大阪      |        880 |
| 岡山      |        190 |
| 広島      |        280 |
| 熊本      |        175 |
| 福岡      |        510 |
+-----------+------------+

mysql> SELECT * FROM employees;
+--------------+--------+
| name         | salary |
+--------------+--------+
| 佐藤次郎     | 270000 |
| 山田太郎     | 300000 |
| 鈴木花子     | 220000 |
| 高橋知子     | 290000 |
+--------------+--------+

CASE式とSELECT

SELECTではprefecturesテーブルを使います。

都道府県の人口を「中国」、「九州」、「近畿」、「その他」で集計したいとします。

これをCASE式をつかって書くと

SELECT CASE name WHEN '兵庫' THEN '近畿'
                 WHEN '大阪' THEN '近畿'
                 WHEN '岡山' THEN '中国'
                 WHEN '広島' THEN '中国'
                 WHEN '熊本' THEN '九州'
                 WHEN '福岡' THEN '九州'
                 ELSE 'その他' END AS district,
                 SUM(population) AS population_sum
FROM prefectures GROUP BY district ORDER BY population_sum;

-- 結果
+-----------+----------------+
| district  | population_sum |
+-----------+----------------+
| 中国      |            470 |
| その他    |            530 |
| 九州      |            685 |
| 近畿      |           1430 |
+-----------+----------------+
4 rows in set (0.00 sec)

CASE式とUPDATE

次はemployeesテーブルを使います。

以下の条件でsalary(給与)を更新したいとします。

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

CASE式を書くと一括で更新することができます。

UPDATE employees 
SET salary = CASE WHEN salary >= 300000 THEN salary * 0.9
                  WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
                  ELSE salary END;

mysql> SELECT * FROM employees;
+--------------+--------+
| name         | salary |
+--------------+--------+
| 佐藤次郎     | 324000 |
| 山田太郎     | 270000 |
| 鈴木花子     | 220000 |
| 高橋知子     | 290000 |
+--------------+--------+
4 rows in set (0.00 sec)