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;
MySQL sort allows null values to be ranked behind numbers