MySQL 如何按列排序并将空记录放在末尾

要通过列排序并将空记录放在末尾，请在MySQL中使用ORDER BY和“IS NULL”。语法如下-

select * from yourTableName order by if(yourColumName = '' or yourColumName is null,1,0),yourColumnName;

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

mysql> create table emptyCellsAtEnd
    -> (
    -> ProductId varchar(100)
    -> );
Query OK, 0 rows affected (0.65 sec)

使用INSERT命令在表中插入一些记录。其中有些记录是空的。查询如下-

mysql> insert into emptyCellsAtEnd values('');
Query OK, 1 row affected (0.23 sec)

mysql> insert into emptyCellsAtEnd values('P-1');
Query OK, 1 row affected (0.21 sec)

mysql> insert into emptyCellsAtEnd values('P-2');
Query OK, 1 row affected (0.14 sec)

mysql> insert into emptyCellsAtEnd values('');
Query OK, 1 row affected (0.15 sec)

mysql> insert into emptyCellsAtEnd values('P-3');
Query OK, 1 row affected (0.18 sec)

mysql> insert into emptyCellsAtEnd values('P-4');
Query OK, 1 row affected (0.09 sec)

mysql> insert into emptyCellsAtEnd values('P-9');
Query OK, 1 row affected (0.11 sec)

mysql> insert into emptyCellsAtEnd values('');
Query OK, 1 row affected (0.15 sec)

mysql> insert into emptyCellsAtEnd values('P-8');
Query OK, 1 row affected (0.17 sec)

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

mysql> select * from emptyCellsAtEnd;

以下是输出结果-

+-----------+
| ProductId |
+-----------+
|           |
| P-1       |
| P-2       |
|           |
| P-3       |
| P-4       |
| P-9       |
|           |
| P-8       |
+-----------+
9 rows in set (0.00 sec)

现在，您可以实施我们之前讨论的将空单元格放在末尾的查询。将空单元格放置在末尾的查询如下-

mysql> select * from emptyCellsAtEnd 
-> order by if(ProductId = '' or ProductId is null,1,0),ProductId;

以下是输出结果-

+-----------+
| ProductId |
+-----------+
| P-1       |
| P-2       |
| P-3       |
| P-4       |
| P-8       |
| P-9       |
|           |
|           |
|           |
+-----------+
9 rows in set (0.00 sec)

阅读更多：MySQL 教程