MySQL中 order by 自定义值排序

1、问题

万事皆有因，先说问题。

数据库表中有一个 status 字段（请忽略规范命名），表示各个状态值，如下

`status` tinyint(4) NOT NULL COMMENT '状态: 1 生成中, 10 生效, 20 完成, -10 失效, -20 停用'

在查询列表时，要求：根据状态排序，生成中-》生效-》完成-》失效-》停用 的顺序去执行。

而我们常见的排序语法为：order by status asc 或者 order by status desc ，这种方式会自然顺序排序，得不到我们预期的效果。

那该怎么办呢？

2、方案

MySQL 中的排序 order by 除了可以用 asc 和 desc，还可以自定义字符串/数字来实现排序。

方法一：field( field1,val1,val2,…)

修改SQL如下：

select * from call_package order by field(`status`,1,10,20,-10,-20) asc // 默认asc

这样子写的话，返回的结果集是按照字段status的1、10、20、-10、-20 进行排序的，当然，也可以对字符串进行排序。

select orderNumber, status from orders order by field(status, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped');

原理：FIELD() 函数是将参数1的字段对后续参数进行比较，并返回1、2、3等等，如果遇到null或者没有在结果集上存在的数据，则返回0，然后根据升序进行排序。

法二：case when … then … when … then … else … end 

修改SQL如下：

select * from call_package order by case when `status`=1 then 1 when `status`=10 then 2 when `status`=20 then 3 when `status`=-10 then 4 when `status`=-20 then 5 else 0 end

3、参考

官方 order by 语法教程：http://www.mysqltutorial.org/mysql-order-by/

未经允许请勿转载：程序喵 » MySQL中 order by 自定义值排序