[LeeCode 数据库刷题] 177. 第 N 高的薪水

题目描述

【LeeCode数据库刷题】177. 第N高的薪水

解题思路

  1. 先查询出以 Salary 降序排序,并去重后的前N行记录
    select distinct Salary 
    from Employee 
    order by Salary desc 
    limit N
  2. 再从该结果集中,找到最小的那一行记录
    select Salary as getNthHighestSalary
    from (
    select distinct Salary 
    from Employee 
    order by Salary desc 
    limit N
    ) b
    order by Salary asc 
    limit 1
  3. 由于题目限制,如果不存在第 n 高的薪水,那么查询应返回 null,那么结果还应该判断N是否大于去重后的记录总数
    select if(
    (
        select count(distinct Salary) 
        from Employee
    ) < N,
    null,
    (
        select Salary as getNthHighestSalary
        from (
            select distinct Salary 
            from Employee 
            order by Salary desc 
            limit N
        ) b
        order by Salary asc 
        limit 1
    )
    )

个人答案

所以最终答案为

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select if(
        (
            select count(distinct Salary) 
            from Employee
        ) < N,
        null,
        (
            select Salary as getNthHighestSalary
            from (
                select distinct Salary 
                from Employee 
                order by Salary desc 
                limit N
            ) b
            order by Salary asc 
            limit 1
        )
    )
  );
END
本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!