1. Title
Nth highest Salary (nth high salary)
2. Address of the topic
https://leetcode.com/problems/nth-highest-salary/
3. Topic content
Compared with this topic, the previous topic "Second highest Salary" is the special case in the n=2. The structure of the employee table is exactly the same as for the two-Problem purpose table:
+----+--------+| Id | Salary |+----+--------+| 1 | 100 | | 2 | 200 | | 3 | |+----+--------+
4. Initializing Database Scripts
Create a database named Leetcode in the MySQL database and execute the following script with the source command in the MySQL command line:
--a databaseuse leetcode named Leetcode must be established before executing the script; DROP TABLE IF EXISTS Employee; CREATE TABLE Employee (Id int not NULL PRIMARY KEY, Salary int); INSERT into employee (ID, Salary) VALUES (1, +), insert into employee (ID, Salary) VALUES (2, $); INSERT into employee ( ID, Salary) VALUES (3, $); INSERT into employee (ID, Salary) VALUES (4, +); INSERT into employee (ID, Salary) VALUES (5, INSERT into Employee (Id, Salary) VALUES (6, 400);
5. Solving SQL
The solution to the previous question can refer to this article blog:http://my.oschina.net/tsybius2014/blog/493086
This problem is similar to the solution of the previous question, that is, the source data is sorted by salary column from large to small, and then the nth row of the sorted result set is taken directly, and the value of the row data salary column is the first n salary. The difference is that the subject needs to be implemented in the form of a function, noting that the limit clause is not directly followed by N-1.
CREATE FUNCTION getnthhighestsalary (n INT) RETURNS intbegin SET n = N-1; RETURN (Select Case if COUNT (Salary) >= 1 then (select DISTINCT Salary from Employee ORDER B Y Salary DESC LIMIT N, 1) ELSE NULL END as nthsalary from Employee); END
END
Leetcode:nth highest Salary-nth high wages