MySQL中 order by 自定义值排序

MySQL中 order by 自定义值排序

MySQL-ORDER-BY-Evaluation.png

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 自定义值排序

点  赞 (0) 打  赏
分享到: