我们可以将IFNULL和MySQL ORDER BY一起使用吗？
您可以将IFNULL和ORDERBY子句一起使用。语法如下-
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) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into IfNullDemo(ProductName,ProductWholePrice,ProductRetailPrice) values('Product-1',99.50,150.50);
mysql> insert into IfNullDemo(ProductName,ProductWholePrice,ProductRetailPrice) values('Product-2',NULL,76.56);
mysql> insert into IfNullDemo(ProductName,ProductWholePrice,ProductRetailPrice) values('Product-3',105.40,NULL);
mysql> insert into IfNullDemo(ProductName,ProductWholePrice,ProductRetailPrice) values('Product-4',NULL,NULL);
mysql> insert into IfNullDemo(ProductName,ProductWholePrice,ProductRetailPrice) values('Product-5',209.90,400.50);使用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)
这是如果为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)