美文网首页
LeetCode 176.Second Highest Sala

LeetCode 176.Second Highest Sala

作者: 扑哧咳哧 | 来源:发表于2018-07-12 15:03 被阅读0次
    Write a SQL query to get the second highest salary from the Employee table.
    
    +----+--------+
    | Id | Salary |
    +----+--------+
    | 1  | 100    |
    | 2  | 200    |
    | 3  | 300    |
    +----+--------+
    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                 |
    +---------------------+
    

    思路:

    1、如果结果为空的话返回null,不为空则返回结果,这个条件可以用mysql的ISNULL函数来实现

    2、结果仅为一条记录,需要用到分页关键字:limit,这里的条件设置为 limit 0,1(注意是0到1);

    3、最后的记录名为SecondHighestSalary,我们在查询语句的最后需要加上一句:as SecondHighestSalary

    4、第二高的薪水,我们可以通过将 select salary where salary < (select max(salary) form Employee)这个语句来获得所有比最大薪水小的薪水值,然后再通过order by desc来进行降序排列,然后用limit关键字来实现就行啦。

    最后的结果如下:

    # Write your MySQL query statement below
    
    select IFNULL((
    
    SELECT salary from employee  where salary < (select max(salary) from employee) ORDER BY salary DESC LIMIT 0,1),
    
    NULL) as SecondHighestSalary
    

    相关文章

      网友评论

          本文标题:LeetCode 176.Second Highest Sala

          本文链接:https://www.haomeiwen.com/subject/djcrpftx.html