心得
這題非常有趣,題目要求寫一預存程式輸入N並取出排名第N名的薪水為多少,這題與178. Rank Scores
差不多,所以可以直接套用。
題目
Write a SQL query to get the nth highest salary from the
Employee
table.
1234567 +----+--------+| Id | Salary |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 |+----+--------+For example, given the above Employee table, the nth highest salary where n = 2 is
200
. If there is no nth highest salary, then the query should returnnull
.
答案
- 這題我是拿
178. Rank Scores
的解法直接套上去取N
1234567891011121314CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINRETURN (# Write your MySQL query statement below.SELECT a.SalaryFROM ( SELECT x.*FROM Employee AS xGROUP BY x.SalaryORDER BY x.Salary DESC) AS zJOIN (SELECT @row_number := 0) AS y) AS aWHERE a.Rank = N);END
- 這解法是由高至低排序了所有的薪水,再用
LIMIT來獲取
Nth(因為LIMIT是從0開始所以N必須先減一),非常易懂的解法。
12345678910111213CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE M INT;SET M = N - 1;RETURN (# Write your MySQL query statement below.SELECT SalaryFROM EmployeeGROUP BY SalaryORDER BY Salary DESCLIMIT M, 1);END
參考: