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

主キーの交換

テーブル定義

use PracticeSQL

create table tbl1_1_5(
    p_key nvarchar(3),
    col_1 int,
    col_2 nvarchar(3)
)

insert into tbl1_1_5(p_key, col_1, col_2) values
    ('a', 1, 'あ'),
    ('b', 2, 'い'),
    ('c', 3, 'う')

-- 主キーの入れ替え(aとbの交換)
update tbl1_1_5
set p_key = case 
    when p_key = 'a' then 'b'
    when p_key = 'b' then 'a'
    else p_key 
end

select * from tbl1_1_5

--p_key    col_1   col_2
--b    1   あ
--a    2   い
--c    3   う

テーブル同士のマッチング(IN, EXISTS)

テーブル定義

use PracticeSQL

create table tbl1_1_6_CourseMaster(
    course_id int,
    course_name nvarchar(10)
)

insert into tbl1_1_6_CourseMaster(course_id, course_name) values
    (1, '経理入門'),
    (2, '財務知識'),
    (3, '簿記検定'),
    (4, '税理士')

create table tbl1_1_6_OpenCourses(
    open_month int,
    course_id int
)

insert into tbl1_1_6_OpenCourses(open_month, course_id) values
    (200706, 1),
    (200706, 3),
    (200706, 4),
    (200707, 4),
    (200708, 2),
    (200708, 4)

-- 講座と開催月の対応表出力（in）
select 
    course_name,
    case
        when course_id in (
            select course_id from tbl1_1_6_OpenCourses where open_month = 200706
        ) then 'o'
        else 'x'
    end as '6月',
    case
        when course_id in (
            select course_id from tbl1_1_6_OpenCourses where open_month = 200707
        ) then 'o'
        else 'x'
    end as '7月',
    case
        when course_id in (
            select course_id from tbl1_1_6_OpenCourses where open_month = 200708
        ) then 'o'
        else 'x'
    end as '8月'
from tbl1_1_6_CourseMaster

--course_name  6月    7月    8月
--経理入門 o   x   x
--財務知識 x   x   o
--簿記検定 o   x   x
--税理士    o   o   o

-- 講座と開催月の対応表出力（exists）
select 
    cm.course_name,
    case
        when exists (
            select course_id from tbl1_1_6_OpenCourses oc
            where open_month = 200706
                and oc.course_id = cm.course_id
        ) then 'o'
        else 'x'
    end as '6月',
    case
        when exists (
            select course_id from tbl1_1_6_OpenCourses oc
            where open_month = 200707
                and oc.course_id = cm.course_id
        ) then 'o'
        else 'x'
    end as '7月',
    case
        when exists (
            select course_id from tbl1_1_6_OpenCourses oc
            where open_month = 200708
                and oc.course_id = cm.course_id
        ) then 'o'
        else 'x'
    end as '8月'
from tbl1_1_6_CourseMaster cm

--course_name  6月    7月    8月
--経理入門 o   x   x
--財務知識 x   x   o
--簿記検定 o   x   x
--税理士    o   o   o

CASE式の中で集約関数をつかう

main_club_flgを掛け持ちでなくてもYとするようにすればよい気もする。

テーブル定義

use PracticeSQL

create table tbl1_1_7_StudentClub(
    std_id int,
    club_id int,
    club_name nvarchar(10),
    main_club_flg nvarchar(1)
)

insert into tbl1_1_7_StudentClub values
    (100, 1, '野球', 'Y'),
    (100, 2, '吹奏楽', 'N'),
    (200, 2, '吹奏楽', 'N'),
    (200, 3, 'バドミントン', 'Y'),
    (200, 4, 'サッカー', 'N'),
    (300, 4, 'サッカー', 'N'),
    (400, 5, '水泳', 'N'),
    (500, 6, '囲碁', 'N')

-- 複数のクラブ掛け持ちの人はメインクラブのclub_idを、
-- そうでない人は所属クラブのclub_idを出力
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_id
from tbl1_1_7_StudentClub
group by std_id

--std_id   main_club_id
--100  1
--200  3
--300  4
--400  5
--500  6