hello-world
webエンジニアのメモ。とりあえずやってみる。

mysqlでcase文を使って特殊なソートを行う

公開日時

まずはサンプルテーブルを作成。

create table sample (id int,name varchar(10), sort int);
insert into sample(id, name, sort) values(1,'a',1),(2,'b',2),(3,'c',0),(4,'d',0),(5,'e',3);

+------+------+------+
| id   | name | sort |
+------+------+------+
|    1 | a    |    1 |
|    2 | b    |    2 |
|    3 | c    |    0 |
|    4 | d    |    0 |
|    5 | e    |    3 |
+------+------+------+

ソート用のキーを管理するカラムを持つ上記のようなテーブルがある場合に、キーの昇順に並べ替えたい時は以下のように単純にorder by sortとすればできます。

select * from sample order by sort asc;

+------+------+------+
| id   | name | sort |
+------+------+------+
|    3 | c    |    0 |
|    4 | d    |    0 |
|    1 | a    |    1 |
|    2 | b    |    2 |
|    5 | e    |    3 |
+------+------+------+

ここで、ちょっと特殊な条件を加えて「ソートキーが0の場合は最後にくるようにしたい」となった場合、どうすればいいか分からなかったので調べてみました。

結論としては、order by の中でcase文を使うことで条件を満たせました。

あまり使う機会はないと思いますが一応メモしておきます。

select * from sample order by (case sort when 0 then 100 else sort end) asc, id;

+------+------+------+
| id   | name | sort |
+------+------+------+
|    1 | a    |    1 |
|    2 | b    |    2 |
|    5 | e    |    3 |
|    3 | c    |    0 |
|    4 | d    |    0 |
+------+------+------+

case文を使って0の場合の値を100に置き換えることでソートの順番を変更しています。

少し無理矢理ですが。。。

おまけ

さらに特殊な事例になりますが、symfonyのORMであるPropelのQueryを使っている場合、直接orderByの中に記述することができなかったので、withColumnでカラム名を別途取得してソートするようにしました。

SampleOrderQuery::create()
  ->withColumn('CASE SampleOrder.Sort WHEN 0 THEN 100 ELSE SampleOrder.Sort END', 'SortKey')
  ->orderBy('SortKey')
  ->orderById()
  ->find();

こんな感じにするとできます。

参考