CASE式について
今日、「達人に学ぶSQL徹底指南書」という本を買って読み始めました。
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2008/02/07
- メディア: 単行本(ソフトカバー)
- 購入: 54人 クリック: 1,004回
- この商品を含むブログ (78件) を見る
CASE式をEXISTSやINと併用するとかっこいい☆
例えば、「友達と一緒に帰った日」を以下のテーブルで保持していたとき、
[friend:友達]
- id : int
- name : varchar
[go_home_with:帰宅ログ]
- day : date
- friend_id : int(FK->Friend)
友達を縦軸、日付を横軸にとって検索ができます。
select name, case when exists ( select 1 from go_home_with H where F.id = H.friend_id and H.day = "2011-10-31" ) then '○' else '×' end as "10/31", case when exists ( select 1 from go_home_with H where F.id = H.friend_id and H.day = "2011-11-01" ) then '○' else '×' end as "11/01", case when exists ( select 1 from go_home_with H where F.id = H.friend_id and H.day = "2011-11-02" ) then '○' else '×' end as "11/02" from friend F ;
結果はこんな風になります。
+--------+-------+-------+-------+ | name | 10/31 | 11/01 | 11/02 | +--------+-------+-------+-------+ | ギイ | × | ○ | × | | 赤池 | ○ | ○ | × | | 三洲 | ○ | × | × | +--------+-------+-------+-------+
例えば10月31日にギイと一緒に帰れなかった(=レコードが存在しない)場合、検索結果がヒットしないんじゃなくて「×」って結果が取得できます。
こういう、「元々縦に並んでいる(行単位に管理しているデータ)ものを横に並べ直す(=列単位に整理)」ってこと、結構あるんです。
だから覚えておいて損はないです(そんなの、テーブル設計がダメ? まあそうかもしれません。)
同じことをEXISTSでなくINでもできます(ごめんなさい、こっちは割愛)。
CASE式のELSEを省略すると"ELSE NULL"と暗黙的に扱われるので注意
タイトルの通り、CASE式のELSEを省略するとNULLと扱われるそうです。
なので、
select id, name, salary old_salary, case when salary > 300000 then salary * 0.9 when salary between 250000 and 280000 then salary * 1.2 end new_salary from emp ;
こんなSQLを書くと、
+----+------+------------+------------+ | id | name | old_salary | new_salary | +----+------+------------+------------+ | 1 | foo | 250000 | 300000.0 | | 2 | bar | 320000 | 288000.0 | | 3 | baz | 130000 | NULL | +----+------+------------+------------+
こういう結果が返ってくるので要注意です。
UPDATE文でこれと同じことをすると、(たぶん)bazのsalaryはそのままにしておきたいはずが、NULLに更新されてしまったら大変ですね。
なお、僕はこれをMySQLで動作確認しています。
Gist: https://gist.github.com/1338356