1. Build the student table and build the table SQL as follows:
Create Table int varchar (int);
2. Insert a few data, including the ID field value is NULL
Insert intoStudentValues(1,'Zhang San',Ten),(2,'John Doe', -),(3,'Harry', in),(NULL,'Zhao Liu', -),(NULL,'Qi Zhou', +);
3. Under our query, you can see that there is a value with an empty ID field:
4. Sort the student table by ID
Method One:
Select*fromorderby-desc;
Method Two:
Select * from Order by IsNull (ID), id;
Method Three:
Select * from Order by COALESCE (ID,999999999999); /* (COALESCE (ID,999999999999) returns 999999999999 when the ID is empty this value) */
COALESCE: Returns the first non-empty expression in a parameter (left-to-right)
There is no NVL () function in MySQL, we use COALESCE () instead. Coalesce compared to the NVL advantage is that the parameters in the coalesce can have multiple, and NVL () parameters only two.
Of course, case when....then....else......end can also be used in Oracle (more powerful, case-when can be followed by an expression)
It is recommended to use method two.
Mysq to sort fields that have null values