mysql排序让空值NULL排在数字后边

从现实项目需求出发;
有一张城市表;
里面有北京、上海、广州、河北、天津、河南6座城市;

mysql> select * from bjy_order;
+----+------+
| id | city |
+----+------+
|  1 | 北京 |
|  2 | 上海 |
|  3 | 广州 |
|  4 | 河北 |
|  5 | 天津 |
|  6 | 河南 |
+----+------+

要求是让上海排第一个、天津排第二个;
最简单粗暴的方法就是添加一个order_number字段;
用来标识顺序的;
然后通过 order by order_number asc 排序;

mysql> select * from bjy_order order by order_number asc;
+----+------+--------------+
| id | city | order_number |
+----+------+--------------+
|  2 | 上海 |            1 |
|  5 | 天津 |            2 |
|  1 | 北京 |            3 |
|  3 | 广州 |            4 |
|  4 | 河北 |            5 |
|  6 | 河南 |            6 |
+----+------+--------------+

这么做确实能满足需求;
但是如果表里面有中国全部的32个省呢?
再如果来个全国的县市表几百个数据呢?
而我们只是想让某几个值排最前面就好了;
就如人们大部分人只知道世界第一高峰是珠穆朗玛峰而不去关注第二第三一样;
我们应该首先想到的就是只给需要排在前面的加上排序数字;其他为NULL;

mysql> select * from bjy_order;
+----+------+--------------+
| id | city | order_number |
+----+------+--------------+
|  1 | 北京 | NULL         |
|  2 | 上海 |            1 |
|  3 | 广州 | NULL         |
|  4 | 河北 | NULL         |
|  5 | 天津 |            2 |
|  6 | 河南 | NULL         |
+----+------+--------------+

然后我们order by一下;

mysql> select * from bjy_order order by order_number asc;
+----+------+--------------+
| id | city | order_number |
+----+------+--------------+
|  1 | 北京 | NULL         |
|  3 | 广州 | NULL         |
|  4 | 河北 | NULL         |
|  6 | 河南 | NULL         |
|  2 | 上海 |            1 |
|  5 | 天津 |            2 |
+----+------+--------------+

然而即将成功的时候让人沮丧的事情发生了;
那些为NULL的排在在最前面;
OK;下面有请今天的主角出场来解决这个问题;
我们来利用is null把sql给稍微改造一下即可;``

mysql> select * from bjy_order order by order_number is null,order_number asc;
+----+------+--------------+
| id | city | order_number |
+----+------+--------------+
|  2 | 上海 |            1 |
|  5 | 天津 |            2 |
|  1 | 北京 | NULL         |
|  3 | 广州 | NULL         |
|  4 | 河北 | NULL         |
|  6 | 河南 | NULL         |
+----+------+--------------+

到此完美实现需求;
白俊遥博客

白俊遥博客
请先登录后发表评论
  • latest comments
  • 总共9条评论
白俊遥博客

:select * from bjy_order order by field(city,'上海','天津') desc

2017-11-15 14:56:36 回复

白俊遥博客

... :可以的,利用多次排序

2017-04-23 16:58:40 回复

白俊遥博客

小雨的叶香伦 :有2个问题问下:1.这里order by 取出前面是空的,加一个where 取出order_number 为空就可以了吧 select * from bjy_order where order_number!='' order by order_number asc;2.select * from bjy_order order by order_number is null,order_number asc;   order_number is  null  ,为什么还可以取出null的情况,不是 is null 情况取出的吗

2016-10-21 12:07:00 回复

白俊遥博客 白俊遥博客

云淡风晴 :那是排序字段;不是where条件;所以能不能取出;和null无关;

2016-10-22 00:39:36 回复

白俊遥博客

鹤山 :白兄,能出一个mysql通过条件和经纬度坐标排序的教程不?

2016-07-08 20:14:06 回复

白俊遥博客 白俊遥博客

云淡风晴 :这个要根据你的业务逻辑来写了;

2016-07-09 16:07:39 回复

白俊遥博客

鹤山 :我是一个mysql小白,有这样一个需求, 一张商家表,一张用户表,用户表里面有自己的经度纬度,商家表也有商家的经度纬度,用户在搜索商家表里面的关键字时,查找出商家,并且需要通过经度纬度已就近原则排序!近的排在前面,远的排在后面。能帮我想想办法吗!

2016-07-13 09:03:11 回复

白俊遥博客 白俊遥博客

云淡风晴 :http://www.thinkphp.cn/extend/561.html

2016-07-14 11:19:03 回复

白俊遥博客

胡勒巴图 :OK

2017-04-26 09:07:24 回复