mysql - 在 SQL 中，当没有要导出的值时，如何输出 "NULL"而不是 "There are no results to be displayed"

Question

现在正在使用 MySQL v8.0。

问题是:

Write an SQL query to report the id and the salary of the second highest salary from theEmployee table. If there is no second highest salary, the query shouldreport null.

我的虚拟数据是:

Create table If Not Exists Employee (id int, salary int);
insert into Employee (id, salary) values 
(1, 100);

我理想的输出是这样的:

+------+--------+
|  id  | salary |
+------+--------+
| NULL |  NULL  |
+------+--------+

我使用 DENSE_RANK 作为一种更直接的方法来解决这个问题:

WITH sub AS (SELECT id,
       salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS num
       FROM Employee )
SELECT id, salary
FROM sub
WHERE num = 2

但是当没有第二高的薪水时，我在导出 NULL 时遇到了问题。我试过 IFNULL，但没有用。我猜这是因为输出实际上不是空的，而是空的。

提前谢谢你。

Answer 1

WITH sub AS (
    SELECT id,
           salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS num
    FROM Employee 
)
SELECT id, salary
FROM sub
WHERE num = 2
UNION ALL
SELECT NULL, NULL
WHERE 0 = ( SELECT COUNT(*)
            FROM sub 
            WHERE num = 2 );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=31f5afb0e7e5dce9c2c128ccc49a6f42