[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 協議》，轉載必須註明作者和本文連結