Leetcode:second highest Salary-the second highest wage

1. Title

Second highest Salary (second highest salary)

2. Address of the topic

https://leetcode.com/problems/second-highest-salary/

3. Topic content

Now there is a table that records the ID (primary key) and salary (payroll), and the second highest salary is found. If there is no second-highest wage, NULL is returned.

+----+--------+| 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:

--The Databaseuse leetcode;drop TABLE IF EXISTS employee named Leetcode must be established before executing the script; CREATE TABLE employee (ID INT not NULL PRIMARY KEY, Salary int.); INSERT into Employee (ID, Salary) VALUES (1, +); Inser T into employee (ID, Salary) VALUES (2, $); INSERT into employee (ID, Salary) VALUES (3, $); INSERT into employee (ID, S Alary) VALUES (4); INSERT into employee (ID, Salary) VALUES (5, +); INSERT into employee (ID, Salary) VALUES (6, 400) ;

5, Problem solving SQL1

The basis for solving this problem is to find the highest wage first, and then the second highest wage (i.e. not equal to the maximum wage).

The maximum wage can be calculated using the following SQL statement:

SELECT MAX (Salary) from Employee;

Then the second highest salary is:

Select Max (Salary) from Employeewhere Salary <> (select Max (Salary) from Employee);

This is the SQL statement that can be AC dropped!

If the problem goes down, you can continue to write for the third and fourth highest wages. These SQL statements are similar in wording. If the third high-wage SQL statement can be written as follows:

Select Max (Salary) from Employeewhere Salary <> (select Max (Salary) from Employee) and Salary <> (select MAX (Salary) from the employee WHERE Salary <> (SELECT MAX (Salary) from employee);

That is not equal to the highest wage within the maximum and second largest wage.

6, Problem solving SQL2

Another easier way is to reorder the source data by salary columns from large to small, and then take the nth row of the sorted result set directly, and the value of the row data salary column is the first n salary. This method can be a lot less than the previous method when the n is a large number of rows of SQL statements.

A section of the SQL statement that can be AC dropped is as follows:

Select Case while COUNT (Salary) >= 1 then (SELECT DISTINCT Salary from Employee ORDER by Salary DESC LI MIT 1, 1) ELSE NULL END as Nthsalaryfrom Employee;

In the sixth line of the above SQL statement, the first number in the upper limit is filled in n-1 to find out the nth salary. This is the second largest wage, so it's written in LIMIT 1, 1

END

Leetcode:second highest Salary-the second highest wage