美文网首页
2020-04-01-(2)

2020-04-01-(2)

作者: DUYAN_bc77 | 来源:发表于2020-04-01 21:30 被阅读0次

    找到Second

    Create table If Not Exists Employee (Id int, Salary int)
    Truncate table Employee
    insert into Employee (Id, Salary) values ('1', '100')
    insert into Employee (Id, Salary) values ('2', '200')
    insert into Employee (Id, Salary) values ('3', '300')
    

    输入

    {"headers": {"Employee": ["Id", "Salary"]}, "rows": {"Employee": [[1, 100], [2, 200], [3, 300]]}
    

    输出

    {"headers": ["SecondHighestSalary"], "values": [[200]]}
    

    Solution 1

    Select Max(distinct Salary) as SecondHighestSalary
    from Employee
    Where Salary < (Select Max(distinct Salary) from Employee)
    

    使用子查询找到最高salary记为SecondHighestSalary
    Max函数返回最大值,distinct去重。

    Select Max(distinct Salary) as SecondHighestSalary
    from Employee
    

    得到第一高薪水。
    再找出小于SecondHighestSalary的即是第二高。
    Select Max

    Select Max(distinct Salary) as SecondHighestSalary
    from Employee
    Where Salary < “第一高”
    

    Solution 2 使用 limit 和 offset

    limit n子句表示查询结果返回前n条数据

    offset n表示跳过x条语句

    limit y offset x 分句表示查询结果跳过 x 条数据,读取前 y 条数据

    使用limit和offset,降序排列再返回第二条记录可以得到第二大的值。

    Select distinct Salary as SecondHighestSalary
    from Employee
    Order by Salary desc #降序排列
    Limit 1,1 #跳过第一条得到第二条
    

    考虑特殊情况 NULL
    题目要求,如果没有第二高的成绩,返回空值,所以这里用判断空值的函数(ifnull)函数来处理特殊情况。

    fnull(a,b)函数解释:

    如果value1不是空,结果返回a
    如果value1是空,结果返回b

    select ifnull (第2步的Salary,null) as 'SecondHighestSalary'
    
    Select ifnull(
        (Select distinct salary
        from Employee
        Order by Salary desc
        Limit 1,1),null) as SecondHighestSalary
    

    相关文章

      网友评论

          本文标题:2020-04-01-(2)

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