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

在SQL中，COALESCE函数用于返回参数列表中的第一个非null表达式。如果所有表达式都为null，则COALESCE返回null。在本文中，我们将探讨如何使用COALESCE函数对含有空值和非空值的结果进行排序。

阅读更多：MySQL 教程

示例

示例数据集

我们将使用以下示例数据集来演示COALESCE函数的用法：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary INT
);

INSERT INTO employees (id, name, age, salary) VALUES
(1, '张三', 25, 5000),
(2, '李四', 30, NULL),
(3, '王五', NULL, 3000),
(4, '赵六', 25, 5500),
(5, '钱七', 35, 6500),
(6, '孙八', 40, 7000),
(7, '周九', 45, 7500),
(8, '吴十', NULL, NULL);

用法示例

假设我们要以员工薪水的降序排列员工，且在薪资相同时按照年龄升序排列。然而，数据集中存在null值。我们可以使用COALESCE函数在排序时将null值转换为一个非null值。下面的示例将COALESCE函数与ORDER BY子句一起使用来实现这一点：

SELECT name, COALESCE(age,0) AS age, COALESCE(salary,0) AS salary
FROM employees
ORDER BY COALESCE(salary,0) DESC, COALESCE(age,0) ASC;

执行上述代码将返回以下结果：

+--------+-----+--------+
|  name  | age | salary |
+--------+-----+--------+
| 周九   | 45  |   7500 |
| 吴十   |  0  |      0 |
| 孙八   | 40  |   7000 |
| 钱七   | 35  |   6500 |
| 赵六   | 25  |   5500 |
| 张三   | 25  |   5000 |
| 王五   |  0  |   3000 |
| 李四   | 30  |      0 |
+--------+-----+--------+

解释

在上面的示例中，我们使用COALESCE函数将age和salary列中的null值转换为0。这样，我们的排序就不会将null值排在前面。在我们的示例中，我们使用了ORDER BY子句将员工按照COALESCE(salary,0)的值降序排序，如果薪资相同，则按照COALESCE(age,0)的值升序排序。在这里，如果salary或age列的值为null，则COALESCE函数返回0，因此具有null值的行将在排序时被认为具有更低的值。

在上述代码中，我们使用的COALESCE函数的形式如下：