本日も「達人に学ぶSQL徹底指南書」を地道に進めていきます。
CASE式の利用
私自身はCHECK制約を使ったことはないですが、正しいテーブル設計においてはこれがあると誤ったデータを入れることを防ぐことができます。
例 会計年度のはじめと終わりを定義したのに、終わりのほうが昔になっているなど。
またCASE式ではCHECK制約と相性がいいです。
使い方については本家を参考に。
CHECK制約はNOT NULLやUNIQUE同様正しい値のみを格納できるので積極的に使っていく必要ありかもです。
制約に関していろいろ↓
【初級編⑨】テーブルに設定するキーの種類や様々な制約（CONSTRAINT）
SQLの制約の種類とその指定方法
UPDATE文のCASE
CASE式は条件分岐にも使えます。
例えば下記のようなテーブルsalarytbがあるとします。
name | salary
——+———
相田 | 200000
田中 | 300000
ここで以下のような条件で更新するとします。
- 30万以上の社員は15％アップ
- 20万以上30万未満(299999以下)の社員は20%アップ
これも1行で以下のように書けます。
|
1 2 3 4 5 6 7 |
UPDATE salarytb set salary = --ここで条件分岐をさせます(対象の値が入るのはここのため) CASE WHEN salary >= 300000 THEN salary * 1.15 WHEN salary BETWEEN 200000 AND 299999 THEN salary * 1.2 ELSE salary END --それ以外はそのまま |
この場合、UPDATE文を2回すると290000の社員は2回昇給をしてしまいます。
テーブル同士のマッチング
CASE式の大きな利点は式を評価でき、bETWEENやLIKE、大小判定などが使えることです。
テーブル名 CourseMaster
course_id | course_name
———–+————-
1 | 経理入門
2 | 財務知識
3 | 簿記検定
4 | 税理士
テーブル名 OpenCourse
month | course_id
——–+———–
200706 | 1
200706 | 3
200706 | 4
200707 | 4
200708 | 2
200708 | 4
ここで下記のような表を作りたいとします。
course_name | 6月 | 7月 | 8月
————-+—–+—–+—–
経理入門 | ○ | × | ×
財務知識 | × | × | ○
簿記検定 | ○ | × | ×
税理士 | ○ | ○ | ○
この場合、下記のようにSQLを書けばOKです。
|
1 2 3 4 5 6 7 |
SELECT course_name, CASE WHEN course_id IN ( SELECT course_id FROM OpenCourses WHERE month = '200706' ) THEN '○' ELSE '×' END AS "6月" , CASE WHEN course_id IN ( SELECT course_id FROM OpenCourses WHERE month = '200707' ) THEN '○' ELSE '×' END AS "7月" , CASE WHEN course_id IN ( SELECT course_id FROM OpenCourses WHERE month = '200708' ) THEN '○' ELSE '×' END AS "8月" FROM CourseMaster |
EXISTSを使っても似たようなものができますね。
コツとしてはまず骨組みとなるSQL(外の部分のSQL)を作ってから細かいもの(中の詳細な条件)を組み立てていくのがいいのかなと思っております。
