第二高薪 [英] Second Highest Salary

问题描述

编写 SQL 查询以从 Employee 表中获取第二高的薪水.

Write a SQL query to get the second highest salary from the Employee table.

    | Id | Salary |
    | 1  | 100    |
    | 2  | 200    |
    | 3  | 300    |

例如，给定上面的 Employee 表，查询应该返回 200 作为第二高的薪水.如果没有第二高的薪水，则查询应返回 null.

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

    | SecondHighestSalary |
    | 200                 |

这是一个来自 Leetcode 的问题，为此我输入了以下代码:

This is a question from Leetcode, for which I entered the following code:

    SELECT CASE WHEN Salary = '' 
                THEN NULL
    ELSE Salary 
    END AS SecondHighestSalary 
    FROM (SELECT TOP 2 Salary
                ,ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Num
          FROM Employee
          ORDER BY Salary DESC) AS T
    WHERE T.Num = 2

它表示如果没有第二高薪水的值，则查询不会返回 NULL.例如.如果表是

It says that the query does not return NULL if there's no value for second highest salary. For eg. if the table is

   | Id | Salary| 
   | 1  |  100  |

查询应该返回

   |SecondHighestSalary|
   |       null        |

而不是

   |SecondHighestSalary|
   |                   |

推荐答案

如果是平局，您需要第二高的不同值.例如.对于值 100、200、300、300，您需要 200.

In case of ties you want the second highest distinct value. E.g. for values 100, 200, 300, 300, you want 200.

所以得到最高值 (MAX(salary) => 300) 然后得到小于那个的最高值:

So get the highest value (MAX(salary) => 300) and then get the highest value less than that:

select max(salary) from mytable where salary < (select max(salary) from mytable);

这篇关于第二高薪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！