为此,请使用order by nullif()。让我们首先创建一个表-mysql> create table DemoTable672(
CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
CustomerName varchar(100),
CustomerAmount int
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable672(CustomerName,CustomerAmount) values('Chris',560);
mysql> insert into DemoTable672(CustomerName,CustomerAmount) values('Robert',null);
mysql> insert into DemoTable672(CustomerName,CustomerAmount) values('',450);
mysql> insert into DemoTable672(CustomerName,CustomerAmount) values('David',456);
mysql> insert into DemoTable672(CustomerName,CustomerAmount) values('Carol',786);
使用select语句显示表中的所有记录-mysql> select *from DemoTable672;
这将产生以下输出-+------------+--------------+----------------+
| CustomerId | CustomerName | CustomerAmount |
+------------+--------------+----------------+
| 1 | Chris | 560 |
| 2 | Robert | NULL |
| 3 | | 450 |
| 4 | David | 456 |
| 5 | Carol | 786 |
+------------+--------------+----------------+
5 rows in set (0.00 sec)
以下是如何通过大小写执行MySQL命令。在这里,我们还使用FIELD()了不区分大小写的搜索-mysql> select *from DemoTable672 ORDER BY NULLIF(CustomerName,'') IS NULL, FIELD(CustomerAmount,560,786,450,456);
这将产生以下输出-+------------+--------------+----------------+
| CustomerId | CustomerName | CustomerAmount |
+------------+--------------+----------------+
| 2 | Robert | NULL |
| 1 | Chris | 560 |
| 5 | Carol | 786 |
| 4 | David | 456 |
| 3 | | 450 |
+------------+--------------+----------------+
5 rows in set (0.04 sec)