SQL中如何输出“NULL”而不是“没有要显示的结果”当没有值可导出时

发布于 2025-01-11 06:25:57 字数 719 浏览 0 评论 0原文

现在使用MySQL v8.0。

问题是：

编写一个 SQL 查询来报告来自第二高薪水的 id 和薪水员工表。如果没有第二高的工资，则查询应该报告为空。

我的虚拟数据是：

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，但没有成功。我想这是因为输出实际上不是空的而是空的。

先感谢您。

原文

Using MySQL v8.0 right now.

The question is:

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

My dummy data is:

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

My ideal output is like this:

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

I used DENSE_RANK as a more straightforward way for me to solve this question:

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

But I have a problem exporting NULL when there's no second highest salary. I tried IFNULL, but it didn't work. I guess it's because the output is not actually null but just empty.

Thank you in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我爱人 2025-01-18 06:25:57

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

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

回复收藏 0 原文

A君 2025-01-18 06:25:57

在我看来，仅使您的查询成为子查询并从单行生成子查询进行左连接似乎是最简单的方法：（

select id, salary
from (select null) at_least_one_row
left join (
    select id, salary
    from (
        select id, salary, dense_rank() over (order by salary desc) as num
        from Employee
    ) ranked_employees
    where num = 2
) second_highest_salary on true

我通常更喜欢子查询而不是仅使用一次的 cte；我发现这很混乱。）

Just making your query a subquery and left joining from a single-row producing subquery seems to me the simplest approach:

select id, salary
from (select null) at_least_one_row
left join (
    select id, salary
    from (
        select id, salary, dense_rank() over (order by salary desc) as num
        from Employee
    ) ranked_employees
    where num = 2
) second_highest_salary on true

(I usually prefer a subquery to a cte that's only used once; I find that obfuscatory.)

回复收藏 0 原文

~没有更多了~