为此,您可以使用ORDER BY IFNULL()
。让我们首先创建一个表-
mysql> create table DemoTable -> ( -> Name varchar(20), -> CountryName varchar(20) -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('Chris',NULL); mysql> insert into DemoTable values('David','AUS'); mysql> insert into DemoTable values(NULL,'UK'); mysql> insert into DemoTable values(NULL,'AUS'); mysql> insert into DemoTable values(NULL,NULL);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+-------+-------------+ | Name | CountryName | +-------+-------------+ | Chris | NULL | | David | AUS | | NULL | UK | | NULL | AUS | | NULL | NULL | +-------+-------------+ 5 rows in set (0.00 sec)
这是实现MySQL ORDER BY x的查询,其中(x = col3如果col3!= null,否则x = col2)-
mysql> select *from DemoTable -> order by ifnull(Name,CountryName);
这将产生以下输出-
+-------+-------------+ | Name | CountryName | +-------+-------------+ | NULL | NULL | | NULL | AUS | | Chris | NULL | | David | AUS | | NULL | UK | +-------+-------------+ 5 rows in set (0.00 sec)