题目描述

解题思路

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 协议》，转载必须注明作者和本文链接