達人に学ぶSQL徹底指南書 1-1 CASE式のススメ①

既存のコード体系を新しい体系に変換して集計する

テーブル定義

use PracticeSQL

create table tbl1_1(
    pref_name nvarchar(50),
    population int
)

insert into tbl1_1(pref_name, population) values
    ('徳島', 100),
    ('香川', 200),
    ('愛媛', 150),
    ('高知', 200),
    ('福岡', 300),
    ('佐賀', 100),
    ('長崎', 200),
    ('東京', 400),
    ('群馬', 50)

-- 県コードを地方コードに再分類する
select 
    case 
        when pref_name = '徳島' then '四国'
        when pref_name = '香川' then '四国'
        when pref_name = '愛媛' then '四国'
        when pref_name = '高知' then '四国'
        when pref_name = '福岡' then '九州'
        when pref_name = '佐賀' then '九州'
        when pref_name = '長崎' then '九州'
        else 'その他'
    end district,
    sum(population) as 人口合計
from tbl1_1
group by
    case
        when pref_name = '徳島' then '四国'
        when pref_name = '香川' then '四国'
        when pref_name = '愛媛' then '四国'
        when pref_name = '高知' then '四国'
        when pref_name = '福岡' then '九州'
        when pref_name = '佐賀' then '九州'
        when pref_name = '長崎' then '九州'
        else 'その他'
    end

--district 人口合計
--その他    450
--九州   600
--四国   650

異なる条件の集計を1つのSQLで行う

テーブル定義

use PracticeSQL

create table tbl1_1_2(
    pref_name nvarchar(50),
    gender int,
    population int
)

insert into tbl1_1_2(pref_name, gender, population) values
    ('徳島', 1, 60),
    ('徳島', 2, 40),
    ('香川', 1, 100),
    ('香川', 2, 100),
    ('愛媛', 1, 100),
    ('愛媛', 2, 50),
    ('高知', 1, 100),
    ('高知', 2, 100),
    ('福岡', 1, 100),
    ('福岡', 2, 200),
    ('佐賀', 1, 80),
    ('佐賀', 2, 20),
    ('長崎', 1, 125),
    ('長崎', 2, 125),
    ('東京', 1, 250),
    ('東京', 2, 150)

-- 男女別人口の集計
select 
    pref_name,
    sum(case when gender = '1' then population else 0 end) as cnt_m,
    sum(case when gender = '2' then population else 0 end) as cnt_f
from tbl1_1_2
group by pref_name

--pref_name    cnt_m   cnt_f
--愛媛   100 50
--香川   100 100
--高知   100 100
--佐賀   80  20
--長崎   125 125
--東京   250 150
--徳島   60  40
--福岡   100 200

CHECK制約の練習

（本当はCASE式を使うのだが、SQLServerだと通らない…）

use PracticeSQL

--給与は20万円以下
create table tbl1_1_3(
    gender int,
    salary int constraint check_salary check(
        salary <= 200000
    )
)

insert into tbl1_1_3 values (1, 199999)
insert into tbl1_1_3 values (2, 200001)

--(1 行処理されました)
--メッセージ 547、レベル 16、状態 0、行 12
--INSERT ステートメントは CHECK 制約 "check_salary" と競合しています。競合が発生したのは、データベース "PracticeSQL"、テーブル "dbo.tbl1_1_3", column 'salary' です。
--ステートメントは終了されました。

条件を分岐させたUPDATE

2回ロジックを通すと給与Down⇒給与Upみたいなことになるので、CASE式で処理するのが吉らしい

テーブル定義

use PracticeSQL

create table tbl1_1_4(
    name nvarchar(50),
    salary int
)

insert into tbl1_1_4(name, salary) values
    ('相田', 300000),
    ('神崎', 270000),
    ('木村', 220000),
    ('斎藤', 290000)

--次のようなupdateを行う
-- 1.現在の給料が30万円以上の社員は、10%の減給とする
-- 2.現在の給料が25万以上28万未満の社員は、20%の昇給とする

update tbl1_1_4
set salary = case 
    when 300000 <= salary then salary * 0.9
    when 250000 <= salary and salary < 280000 then salary * 1.2
    else salary 
end

--name salary
--相田   270000
--神崎   324000
--木村   220000
--斎藤   290000