Public account: You and the cabin by: Peter Editor: Peter

Hello, I’m Peter

Today continue to brush leetcode-for-SQL second question: the second highest salary.

The title

The specific description of the topic is as follows:

The answer

Method 1

The second highest salary is the highest in the remaining salary after the highest salary.

select 
    max(Salary) as SecondHighestSalary  2. After excluding the highest salary in the original data, the remaining maximum is the second highest
from Employee 
where  Salary < (select max(Salary) from Employee);  -- This select is the highest salary in the original data
Copy the code

Cons: Only need to nest a layer when seeking the second highest salary; If you want the third highest, you have to exclude the first and second highest, you have to exclude them twice

select 
	max(Salary) as ThirdHighestSalary  -- 3, determine the third height
from Employee 
where Salary < ( 
    select 
      max(Salary) as SecondHighestSalary  -- 2. Find the second highest
    from Employee 
    where  Salary < (select max(Salary) from Employee);  -- 1. Find the first height
);
Copy the code

Method 2

Select * from Student; select * from Student; select * from Student;

mysql> select * from Student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   |zhao| 1990- 01- 01 | 男    |
| 02   |Money is power| 1990- 12- 21 | 男    |
| 03   |Sun Feng| 1990- 05- 20 | 男    |
| 04   |Liu yun| 1990- 08- 06 | 男    |
| 05   |Zhou Mei| 1991- 12- 01 | 女    |
| 06   |nuss| 1992- 03- 01 | 女    |
| 07   |Zheng Zhu| 198907 -. | 女    |
| 08   |Wang Ju| 1990- 01- 20 | 女    |
+------+--------+------------+-------+
8 rows in set (0.00 sec)
Copy the code

1, use the form of limit m,n: m indicates the data after the MTH line, excluding the MTH line, and the data after the MTH line:

mysql> select * from Student limit 2.3;   -- 3 rows of data after line 2 (excluding 2)
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   |Sun Feng| 1990- 05- 20 | 男    |
| 04   |Liu yun| 1990- 08- 06 | 男    |
| 05   |Zhou Mei| 1991- 12- 01 | 女    |
+------+--------+------------+-------+
3 rows in set (0.00 sec)
Copy the code

2. Use the limit M offset N format: n data is skipped in the query result and the first M data is read

mysql> select * from Student limit 3 offset 2;   -- Same effect as above
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   |Sun Feng| 1990- 05- 20 | 男    |
| 04   |Liu yun| 1990- 08- 06 | 男    |
| 05   |Zhou Mei| 1991- 12- 01 | 女    |
+------+--------+------------+-------+
3 rows in set (0.00 sec)
Copy the code

Having introduced the use of limit, we use it to fulfill the requirements in this case:

select
	distinct Salary  - to heavy
from Employee
order by Salary desc   -- Salary in descending order
limit 1 offset 1   Display one line after the first data: display one line after removing the highest salary, which is the second highest salary
Copy the code

If there is only one highest value in the original data, that is, there is no second highest salary, it needs to display null, we use ifNULL function to achieve the above result:

select ifnull((select distinct Salary   -- Assigns null if it does not exist
              from Employee
              order by Salary desc   
              limit 1 offset 1), null) as SecondHighestSalary
Copy the code

Find the NTH highest grade

Now, to extend the above problem, assume that there are two tables: Score and Course, which can be correlated by the field c_id.

Now there is a need: find the second highest grade and student number in Chinese subject.

mysql> select * from Score;   Score: student ID s_id, course ID c_id, and Score s_score
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      96 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      81 |
| 03   | 03   |      85 |
| 04   | 01   |      50 |
| 04   | 02   |      40 |
| 04   | 03   |      30 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      43 |
| 06   | 03   |      56 |
| 07   | 02   |      89 |
| 07   | 03   |      94 |
+------+------+---------+
18 rows in set (0.00 sec)
Copy the code
mysql> select * from Course;   -- Course schedule: course number c_id, course name c_name, teacher number t_id
+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
| 01   |Chinese language and literature| 02   |
| 02   |mathematics| 01   |
| 03   |English| 03   |
+------+--------+------+
3 rows in set (0.00 sec)
Copy the code

Correlating the two tables, we can see the following result, we find that the highest subject in Chinese is 80, and the second highest subject is 76, which is the result we want.

select 
	s.*,
	c.c_name 
from Score s 
left join Course c on s.c_id=c.c_id;
Copy the code

select 
	distinct s.s_score  -- Score weight reduction
from Score s 
left join Course c on s.c_id=c.c_id
where c.c_name = 'Chinese'  -- Assigned subjects
order by s.s_score desc  - in descending order
limit 1 offset 1;  -- Limit and offset enable page turning
Copy the code