MySQL是否可以在ORDER BY中使用IFNULL？

您可以在ORDER BY子句中使用IFNULL。语法如下 –

SELECT * FROM yourTableName ORDER BY IFNULL(yourColumnName1, yourColumnName2);

为了理解上面的语法，让我们创建一个表。创建表的查询语句如下 –

mysql> CREATE TABLE IfNullDemo (
      Id int NOT NULL AUTO_INCREMENT,
      ProductName varchar(10),
      ProductWholePrice float,
      ProductRetailPrice float,
      PRIMARY KEY(Id));
Query OK, 0 rows affected (1.19 sec)

使用INSERT命令在表中插入一些记录。查询语句如下 –

mysql> INSERT INTO IfNullDemo(ProductName, ProductWholePrice, ProductRetailPrice) VALUES('Product-1', 99.50, 150.50);
Query OK, 1 row affected (0.21 sec)
mysql> INSERT INTO IfNullDemo(ProductName, ProductWholePrice, ProductRetailPrice) VALUES('Product-2', NULL, 76.56);
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO IfNullDemo(ProductName, ProductWholePrice, ProductRetailPrice) VALUES('Product-3', 105.40, NULL);
Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO IfNullDemo(ProductName, ProductWholePrice, ProductRetailPrice) VALUES('Product-4', NULL, NULL);
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO IfNullDemo(ProductName, ProductWholePrice, ProductRetailPrice) VALUES('Product-5', 209.90, 400.50);
Query OK, 1 row affected (0.14 sec)

使用SELECT语句显示表中的所有记录。查询语句如下 –

mysql> SELECT * FROM IfNullDemo;

以下是输出结果 –

+----+-------------+-------------------+--------------------+
| Id | ProductName | ProductWholePrice | ProductRetailPrice |
+----+-------------+-------------------+--------------------+
|  1 | Product-1   |               99.5 |              150.5 |
|  2 | Product-2   |               NULL |               76.56 |
|  3 | Product-3   |              105.4 |                NULL |
|  4 | Product-4   |               NULL |                NULL |
|  5 | Product-5   |              209.9 |               400.5 |
+----+-------------+-------------------+--------------------+
5 rows in set (0.02 sec)

以下是按if null排序的查询 –

mysql> SELECT * FROM IfNullDemo ORDER BY IFNULL(ProductWholePrice, ProductRetailPrice);

以下是输出结果 –

+----+-------------+-------------------+--------------------+
| Id | ProductName | ProductWholePrice | ProductRetailPrice |
+----+-------------+-------------------+--------------------+
|  4 | Product-4   |               NULL |                NULL |
|  2 | Product-2   |               NULL |               76.56 |
|  1 | Product-1   |               99.5 |              150.5 |
|  3 | Product-3   |              105.4 |                NULL |
|  5 | Product-5   |              209.9 |               400.5 |
+----+-------------+-------------------+--------------------+
5 rows in set (0.00 sec)

阅读更多：MySQL 教程