【LeeCode 資料庫刷題】177. 第 N 高的薪水

pigzzz發表於2019-12-20

【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

相關文章