MySQL sort allows null values to be ranked behind numbers

Starting from the realistic project demand;

There is a city table, there are Beijing, Shanghai, Guangzhou, Hebei, Tianjin, Henan 6 cities;

Mysql> SELECT * FROM bjy_order;+----+------+| ID |  City |+----+------+| 1 |  Beijing | | 2 |  Shanghai | | 3 |  guangzhou | | 4 |  Hebei | | 5 |  Tianjin | | 6 | Henan |+----+------+

The requirement is to let Shanghai row the first, Tianjin row second;

The simplest and most brutal method is to add a order_number field, which identifies the order;order by order_number asc 排序


mysql> select * from bjy_order order by  order_number asc;+----+------+--------------+| id | city | order_number  |+----+------+--------------+|  2 |  Shanghai  |             1 | |   5 |  Tianjin  |            2  | |   1 |  Beijing  |            3  | |   3 |  Guangzhou  |            4  | |   4 |  Hebei  |            5  | |   6 |  Henan  |            6  |+----+------+--------------+ 

It does satisfy demand, but what if there are all the 32 provinces in China?

If you come to a national county and city table hundreds of data? And we just want to get some values in front of the line;

Just as most people only know that the first peak of the world is Mt. Everest without paying attention to the second third;

The first thing we should think about is to just give the sort numbers that need to be in front, and the other is null;

Mysql> SELECT * FROM bjy_order;+----+------+--------------+| ID | City |  Order_number |+----+------+--------------+| 1 | Beijing |  NULL | | 2 |            Shanghai |  1 | | 3 | guangzhou |  NULL | | 4 | Hebei |  NULL | | 5 |            Tianjin |  2 | | 6 | Henan | NULL |+----+------+--------------+

Then we order by a bit;

Mysql> SELECT * from Bjy_order ORDER by Order_number asc;+----+------+--------------+| ID | City |  Order_number |+----+------+--------------+| 1 | Beijing |  NULL | | 3 | guangzhou |  NULL | | 4 | Hebei |  NULL | | 6 | Henan |  NULL | | 2 |            Shanghai |  1 | | 5 |            Tianjin | 2 |+----+------+--------------+

But what is frustrating when it comes to success is that the null is at the front;

OK, here is the main character of today to solve this problem;

Let's take advantage.is null把sql给稍微改造一下即可；

Mysql> SELECT * from Bjy_order ORDER by Order_number is null,order_number asc;+----+------+--------------+| ID | City |  Order_number |+----+------+--------------+| 2 |            Shanghai |  1 | | 5 |            Tianjin |  2 | | 1 | Beijing |  NULL | | 3 | guangzhou |  NULL | | 4 | Hebei |  NULL | | 6 | Henan | NULL |+----+------+--------------+

To achieve the perfect demand;

650) this.width=650, "alt=" Bai Jun Remote Blog "src=" http://www.baijunyao.com/Upload/image/ueditor/20151207/1449418392467010. PNG "title=" Bai Jun Haruka Blog "/>

MySQL sort allows null values to be ranked behind numbers