mysql 並び換えのルールを作りたい（order,case）

基本

▼orderでcase文を使う

order by (
	case カラム名
		when 値 then 1
		when 値 then 2
	else 3 end
)

▼逆順

order by (
	case カラム名
		when 値 then 1
		when 値 then 2
	else 3 end
) desc

例：服のサイズの場合

▼「XS, S, M, L, XL」としたい

order by (
	case カラム名
		when "XS" then 1
		when "S"  then 2
		when "M"  then 3
		when "L"  then 4
		when "XL" then 5
	else 999 end
)

一文字目で何かしたい

「部屋番号」が、「101, 203, B01」などのように格納されている。
上階から順にソートしたいが、「order by 部屋番号 desc」だと「B01, 203, 101」となる。

ルール

方法はいくつかあると思うけど、ひとまず頭に任意の文字を追加してソートさせる

▼今回は、「101→20_101」「203→10_203」「B01→90_B01」となるようにする
・「B01」や「101」の一文字目で判断する→「SUBSTRING()」で一文字目を取得
・「90_」や「10_」を追加する→「concat()」で文字を連結

記述

order by (
	case SUBSTRING(部屋番号, 1, 1)
		when "2" then concat("10_", 部屋番号)
		when "1" then concat("20_", 部屋番号)
		when "B" then concat("90_", 部屋番号)
	else concat("99_", 部屋番号) end
)

※「else」は無くてもいいんだけど、万が一を考慮しておく

「when "2" then concat("10_", 部屋番号)」ってトコをもう少しスマートに書きたいけど・・・ひとまず保留。

地下とその他で分岐させる場合

▼地下を「10_」、地上階を「90_」とし、逆順にする

order by (
	case SUBSTRING(部屋番号, 1, 1)
		when "B" then concat("10_", 部屋番号)
	else concat("90_", 部屋番号) end
) desc

※「A、C～Z」などから始まるものは考慮しない

この場合、「90_101」「90_203」「10_B01」となり、逆順で「90_203」「90_101」「10_B01」となる。
でも、部屋番号が「101、201、203、B01」となった場合「90_101」「90_201」「90_203」「10_B01」となり、逆順にすると「90_203」「90_201」「90_101」「10_B01」となってしまう。

「101、201、203、B01」を
・「203, 201, 101, B01」としたいなら問題なし
・「201, 203, 101, B01」としたいなら問題あり

特定の値を先にして・・・ちょっと複雑なヤツ

元データ

item_code	item_name	item_price	order_count
A01	鳥精	140	2
A02	ポンポチ	150	1
A03	ささみおろしポン酢	150	0
B01	豚精	140	3
B02	豚シソ巻	145	0
B03	豚ヒレ	200	0
C01	カルビ	150	0
D01	丸ごとシマウマ	140	4
D02	餅ベーコン	100	2
D03	カチョカバロ	200	0

並び順のルール

・order_count=0を最初。
・order_count=0はitem_priceが大きい順。
・order_count=0でitem_priceが同じならitem_code順。
・「0 < order_count」はorder_countが大きい順 ・order_countが同じならitem_priceの大きい順にしたい

並びかえた結果

item_code	item_name	item_price	order_count
B03	豚ヒレ	200	0
D03	カチョカバロ	200	0
A03	ささみおろしポン酢	150	0
C01	カルビ	150	0
B02	豚シソ巻	145	0
D01	丸ごとシマウマ	140	4
B01	豚精	140	3
A01	鳥精	140	2
D02	餅ベーコン	100	2
A02	ポンポチ	150	1

order句のサンプル

order by
( # order_count=0を最初にして、item_priceが大きい順
  case order_count
    when 0
      then item_price # order_count=0はitem_priceが大きい順。
    else
      -1              # 「0 < order_count」は最後になるようにしておく(※order_count=0を最初にするため)
  end
) desc,
# order_count=0を最初にして、item_priceが同じなら、item_code順。
  case order_count
    when 0
      then item_code
    else
      zzz
  end
) asc,
order_count desc,
item_price desc