受講料が最大70%OFF 受講料が最大70%OFF

SQLのCASE式の便利な使い方をサンプルつきでまとめました。

なお、MySQLのサンプルデータベースEmployeesを、SQL実行結果の表示にはphpMyAdminを使って解説しています。

Employeesテーブルの構造は、 Employees Structureを参照してください。

CASE式で、SQL内の分岐や比較ができる

書式は以下の通り。

CASE case_value
WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list] ...
	[ELSE statement_list]
END CASE

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.6.5.1 CASE 構文

SQL内だけで、分岐や比較処理ができる便利な構文です。

実際に、使ってみましょう。

employeesデータベースの、departments(部署)テーブルの「Sales」を「営業部」、その他を「それ以外」としてデータをselectしてみます。

departmentsテーブル

実行するSQLクエリは以下の通り。

SELECT `dept_no`, 
case `dept_name`
	when 'Sales' then '営業部'
	else 'それ以外'
end
as jpname_dept
FROM `departments`

結果は、こうなります。

結果

上記の書き方を「単純case式」と言います。

caseで比較するカラムが1つだけなら、これでいいのですが、複数カラムを使った条件の場合はどうすればよいのでしょうか?

CASEの条件を複数指定する例

employeesテーブルと、dept_empテーブルを結合して、社員名と入社年月日、退職年月日を抽出しました。

SELECT 
	employees.first_name, 
	employees.last_name, 
	dept_emp.from_date, 
	dept_emp.to_date 
FROM 
	dept_emp, 
	employees 
WHERE 
	dept_emp.emp_no = employees.emp_no

結果は以下の通りです。to_date(退職日)に「9999-01-01」が入っている社員は、在籍中ということを表しています。

2000年以前入社で在籍中の社員は「長期在籍中」、退職済みの社員は退職年月日、それ以外の社員は「在籍中」としてを抽出するSQLを作ってみましょう。

SELECT 
	employees.first_name, 
	employees.last_name, 
case
	when dept_emp.from_date < '2000-01-01' and dept_emp.to_date = '9999-01-01' then '長期在職中'
	when dept_emp.to_date != '9999-01-01' then dept_emp.to_date
	else '在職中'
end as zaishoku
FROM 
	`dept_emp`, 
	employees 
WHERE 
	dept_emp.emp_no = employees.emp_no

結果はこうなります。

order by にCASEを指定する例

値の大小でもなく、辞書順でもなく、決めた順番でデータを抽出したい場合にCASEを使うと便利です。

SELECT `dept_no`, `dept_name` FROM `departments` 
order by
case
    when dept_name='Sales' then 1
    when dept_name='Development' then 2
    when dept_name='Finance' then 3
    when dept_name='Production' then 4
    when dept_name='Customer Service' then 5
    when dept_name='Human Resources' then 6
    when dept_name='Quality Management' then 7
    when dept_name='Research' then 8
    when dept_name='Marketing' then 9
    else 99
end

dept_name(部署名)ごとに手動で番号を振り、指定順に並べ替えて表示してみましょう。指定外の部署名が出てきた場合、「else 99」の行により、99が割り振られます。

結果

UPDATEでCASEを使って、指定した値を入れ替える

「指定した値を入れ替える」って、意外と難しいですよね。

例えば以下の、employeesテーブルのgender(性別)をM→F、F→Mに入れ替える場合、けっこう悩みませんか?

CASEを使うと、比較的かんたんに実現できます。

UPDATE 
	employees 
SET 
	gender = 
    case 
        when gender='M' then 'F'
        when gender='F' then 'M'
        else gender
    end
WHERE 
	gender in ('M','F')

実行結果は以下のとおり。

where句の指定は今回あまり意味がないのですが、大量データの入ったテーブルに対して、余計なレコードを更新しないようにするための対策です。

GROUP BYにCASEを使ってcount集計する例

縦に長い(縦持ち)テーブルを、横に長い(横持ち)テーブルに変換して抽出する例です。

employeesデータベースのtitlesテーブルを使って試してみましょう。

titlesテーブルには、社員番号とタイトル(肩書)、その肩書を持っていた期間が記録されています。人によって、Engineer→Senior Engneerなど肩書が変わった人もいるんですね。

過去に持っていた肩書を横持ちテーブルに変換して抽出してみます。

SELECT 
	emp_no, 
    count(case when title='Staff' then 1 else null end) as flg_staff ,
    count(case when title='Senior Staff' then  1 else null end) as flg_sstaff ,
    count(case when title='Engineer' then 1 else null end) as flg_eng ,
    count(case when title='Assistant Engineer' then 1 else null end) as flg_aeng ,
    count(case when title='Senior Engineer' then 1 else null end) as flg_seng 
FROM 
	titles 
group by emp_no

結果はこうなります。

たとえば、emp_no=10004の社員は、EngneerとSenior Engineerの肩書を持った経験がある、ということになります。

処理としては、肩書がある場合は1,ない場合はnullをCASE式で抽出し、countで数を数えています。nullはcountしても0になります。

CASE式のデメリット

アプリ側で処理させなくても、プログラム的な処理をSQLで出来るCASE式。とても便利なのですが、処理速度を低下させる要因の一つでもあります。

たとえば、Microsoft SQLServerの例ですが、case文を使ってNULL判定と表示結果のフォーマットをおこなったところ、12秒の処理が80秒かかってしまったというケースもあるようです。

参考)SQLクエリにCASE文を加えると非常に遅くなる

CASE式使用で、あまりにもパフォーマンスが落ちる場合は、処理をアプリ側に移すなどのチューニングが必要になることも覚えておきましょう。

 

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。