테이블과 결과
Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
Output:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
문제
- Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.
답
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE startNum INT;
SET startNum = N - 1;
RETURN (
SELECT DISTINCT e.Salary AS distinctSalary
FROM Employee e
ORDER BY distinctSalary DESC
LIMIT startNum, 1
);
END