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();
こんな感じにするとできます。