MySQL COALESCE如何处理NULL和非NULL值的结果排序？

COALESCE()函数优先找到非NULL值。如果开始就找到非NULL值，则返回该值，否则继续查找非NULL值。

首先创建一个表 –

mysql> create table DemoTable
(
   Number1 int,
   Number2 int
);
Query OK, 0 rows affected (5.48 sec)

使用insert命令在表中插入一些记录 –

mysql> insert into DemoTable values(100,200);
Query OK, 1 row affected (0.40 sec)
mysql> insert into DemoTable values(NULL,50);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(10,NULL);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(NULL,NULL);
Query OK, 1 row affected (0.08 sec)

使用select语句显示表中的所有记录 –

mysql> select *from DemoTable;

这将产生以下输出 –

+---------+---------+
| Number1 | Number2 |
+---------+---------+
|     100 |     200 |
|    NULL |      50 |
|      10 |    NULL |
|    NULL |    NULL |
+---------+---------+
4 rows in set (0.00 sec)

使用COALESCE对记录进行排序的查询如下 –

mysql> select coalesce(Number1,Number2) AS NON_NULL_ARGUMENT_FIRST from DemoTable;

这将产生以下输出 –

+-------------------------+
| NON_NULL_ARGUMENT_FIRST |
+-------------------------+
|                     100 |
|                      50 |
|                      10 |
|                    NULL |
+-------------------------+
4 rows in set (0.00 sec)

阅读更多：MySQL 教程