1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

MySQLでcase式を扱う

Last updated at Posted at 2020-09-25

構文には以下の2通りがあるようだ。

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

まず1つ目の構文を試してみた

select shohin_mei,
case shohin_bunrui
    when "衣服" then concat ("A:", shohin_bunrui)
    when "事務用品" then concat ("B:", shohin_bunrui)
    when "キッチン用品" then concat ("C:", shohin_bunrui)
    else null
end as concat_shohin_bunrui
from Shohin;

スクリーンショット 2020-09-25 13.46.36.png

2つ目の構文

select shohin_mei,
case 
    when shohin_bunrui = "衣服" then concat ("A:", shohin_bunrui)
    when shohin_bunrui = "事務用品" then concat ("B:", shohin_bunrui)
    when shohin_bunrui = "キッチン用品" then concat ("C:", shohin_bunrui)
    else null
end as concat_shohin_bunrui
from Shohin;

スクリーンショット 2020-09-25 13.51.05.png

###case式を使った行列変換
以下のようなcase式を使わないgroup byでの出力では行として出力される。

select shohin_bunrui, sum(hanbai_tanka)
from Shohin
group by shohin_bunrui;

スクリーンショット 2020-09-25 14.05.57.png

case式を使うとこで列として出力することもできる。

select 
sum(case when shohin_bunrui = "衣服" then hanbai_tanka else 0 end) as sum_ihuku,
sum(case when shohin_bunrui = "事務用品" then hanbai_tanka else 0 end) as sum_jimu_yohin,
sum(case when shohin_bunrui = "キッチン用品" then hanbai_tanka else 0 end) as sum_kitchen_yohin
from Shohin;

スクリーンショット 2020-09-25 14.10.20.png

以下のようなsqlでは、値段ごとの列を作り、数を数えることもできる。

select 
sum(case when hanbai_tanka <= 1000 then 1 else 0 end) as low_price,
sum(case when hanbai_tanka <= 3000 then 1 else 0 end) as mid_price,
sum(case when hanbai_tanka >= 3001 then 1 else 0 end) as high_price
from Shohin;

スクリーンショット 2020-09-25 14.37.22.png

こちらを参考にさせていただきました。
SQL 第2版 ゼロからはじめるデータベース操作

MySQL 5.6 リファレンスマニュアル

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?