MySQLでカラム内容によって条件分岐させたい
MySQLでカラムの内容によって条件を分岐させたかった。またPHPで処理するのではなくMySQL文で完結させたかった。調べてみるとCASE文というのが使えそうだったので使い方と検証のメモ。
検証内容
楽天やamazonで買い物をし、その際にポイントが発生したとする。このポイントを性別や購入ジャンル、サイトによって以下の通り増加する。
男性→ポイント1.1倍
女性→ポイント1.2倍
amazonでdrinkを購入→1.2倍
amazonでfoodを購入→1.5倍
楽天でdrinkを購入→1.3倍
楽天でfoodを購入→1.6倍
最終的に性別毎の合計ポイントを算出したい。
テーブルの作成
以下テーブルを作成した。
1 2 3 4 5 6 7 8 9 10 |
+-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | point | int(11) | NO | | 0 | | | sex | tinyint(4) | NO | | 0 | | | site | varchar(128) | NO | | NULL | | | genre | varchar(128) | NO | | NULL | | | ymd | datetime | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ |
idは通し番号、 pointは発生したポイント、sexは0が男性で1が女性、siteは購入サイト、genreは購入ジャンル、ymdは購入日とする。
適当にデータを作成する。作成後は以下の通り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
+----+-------+-----+---------+-------+---------------------+ | id | point | sex | site | genre | ymd | +----+-------+-----+---------+-------+---------------------+ | 1 | 100 | 0 | amazon | food | 2016-11-01 00:00:00 | | 2 | 200 | 0 | amazon | food | 2016-11-01 00:00:00 | | 3 | 300 | 0 | rakuten | food | 2016-11-01 00:00:00 | | 4 | 120 | 0 | amazon | drink | 2016-11-01 00:00:00 | | 5 | 220 | 0 | rakuten | drink | 2016-11-01 00:00:00 | | 6 | 220 | 1 | rakuten | drink | 2016-11-01 00:00:00 | | 7 | 120 | 1 | amazon | drink | 2016-11-01 00:00:00 | | 8 | 300 | 1 | rakuten | food | 2016-11-01 00:00:00 | | 9 | 200 | 1 | amazon | food | 2016-11-01 00:00:00 | | 10 | 100 | 1 | amazon | food | 2016-11-01 00:00:00 | +----+-------+-----+---------+-------+---------------------+ |
また、この時点での合計ポイントは以下の通り。
1 2 3 4 5 6 7 8 9 10 |
SELECT sex, SUM(point) AS point FROM point_table GROUP BY sex; +-----+-------+ | sex | point | +-----+-------+ | 0 | 940 | | 1 | 940 | +-----+-------+ |
CASE文を使ってみる
まずは男性→1.1倍、女性1.2倍を算出してみる。SQL文と結果は以下の通り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT sex, SUM( point * ( CASE sex WHEN 0 THEN 1.1 WHEN 1 THEN 1.2 END ) ) AS point FROM point_table GROUP BY sex; +-----+--------+ | sex | point | +-----+--------+ | 0 | 1034.0 | | 1 | 1128.0 | +-----+--------+ |
最初よく分からなかったけどCASE後のカラムが○○だった場合××に置き換えるみたいな形っぽい。上記だとsexカラムが0だった場合1.1に置き換えて、その前のpointと掛け算するという形。
さらにサイトと購入ジャンルで分岐させたい場合は入れ子にして使う。以下の通り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT sex, SUM( point * ( CASE sex WHEN 0 THEN 1.1 WHEN 1 THEN 1.2 END ) * ( CASE site WHEN 'amazon' THEN CASE genre WHEN 'drink' THEN 1.2 WHEN 'food' THEN 1.5 END WHEN 'rakuten' THEN CASE genre WHEN 'drink' THEN 1.3 WHEN 'food' THEN 1.6 END END ) ) AS point FROM point_table GROUP BY sex; |
凄く分かりづらい。CASEでsiteがamazonだった場合で尚且つgenreがdrinkだった場合は1.2倍、foodだった場合は1.5倍という形になる。
リファレンス
https://dev.mysql.com/doc/refman/5.6/ja/case.html
その他
分かりづらいけど使い道は多そう。Where句とかは普通に追加して検索できる。
関連記事
-
-
MySQLでカラム数の異なるテーブルを結合する(UNION)
MySQLでカラム数の異なるテーブル同士をUNIONで結合させたかった。ただ、何 ...
-
-
MySQLでJOINの高速化
JOINで無茶苦茶悩んだのでメモ。やりたかった事は、MySQLで テーブルAには ...
-
-
MySQLでホスト指定しても正常に接続できない場合
MySQL自体は動いており、ユーザーも登録されているのに、ホスト名を指定しても正 ...
-
-
MySQLでサイズの大きいファイルのインポート
MySQLでサイズの大きいdumpファイルを異なるサーバに移行したかった。また移 ...
-
-
phpMyAdminより高速で軽量なAdminerが凄い
MySQLをGUIで管理できるphpMyAdmin。特に不満はないんだけど、もっ ...
-
-
MySQLのUNION
$sql_1 = "SELECT name_a,name_b FROM tabl ...
-
-
MySQLでデータがあれば上書き、無ければ挿入する
既存のソースを編集時に「REPLACE INTO~~」 という見たことの無いSQ ...
-
-
エックスサーバーでMySQLのサイズに注意
エックスサーバーで自動更新のログをMySQLに保存するようなシステムを動かしてい ...
-
-
MySQLの値について
既存プログラムの解析をしてて知ったこと。 MySQLの構造で「int(11)」と ...
-
-
MySQLでserver has gone~というエラー
MySQLで「MySQL server has gone away」というエラー ...
- PREV
- formで送信後にアンカータグをつけたい
- NEXT
- PHPでソーシャル(SNS)ログインする方法