LeetCode - SQL-176 - Вторая по величине зарплата

база данных

Общественный номер: You Er Hut
Автор: Питер
Редактор: Питер

Привет, меня зовут Питер

Продолжайте решать второй вопрос LeetCode-for-SQL сегодня:вторая по величине зарплата.

тема

Конкретное описание темы выглядит следующим образом:

Отвечать

метод 1

Идея метода 1: вторая по величине зарплата, то есть после удаления самой высокой зарплаты самая высокая оставшаяся зарплата (собственный метод);

select 
    max(Salary) as SecondHighestSalary  -- 2、排除原数据中最高薪水之后，剩下的最大值就是第二高
from Employee 
where  Salary < (select max(Salary) from Employee);  -- 1、这个select是找到原始数据中的最高薪水

Недостатки: при поиске второй по величине зарплаты требуется только один слой вложенности; если ищется третья по величине зарплата, то необходимо одновременно исключить первую и вторую по величине зарплаты, которые необходимо исключить дважды

select 
	max(Salary) as ThirdHighestSalary  -- 3、确定第3高
from Employee 
where Salary < ( 
    select 
      max(Salary) as SecondHighestSalary  -- 2、找到第二高
    from Employee 
    where  Salary < (select max(Salary) from Employee);  -- 1、找到第一高
);

Способ 2

Используйте ключевое слово limit для реализации обработки перелистывания страниц Как использовать limit: Предположим, есть таблица Student, и все данные в таблице следующие:

mysql> select * from Student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-02 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+
8 rows in set (0.00 sec)

1. Используйтеlimit m,nФорма: m означает от m-й строки данных, исключая m-ю строку, после n строк данных:

mysql> select * from Student limit 2, 3;   -- 第2行之后（不包含2）的3行数据
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
+------+--------+------------+-------+
3 rows in set (0.00 sec)

2. Используйтеlimit m offset nФорма: указывает, что результат запроса пропускает n фрагментов данных и считывает первые m фрагментов данных.

mysql> select * from Student limit 3 offset 2;   -- 效果同上
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
+------+--------+------------+-------+
3 rows in set (0.00 sec)

После введения использования limit мы используем его для достижения требований в этом вопросе:

select
	distinct Salary  -- 去重
from Employee
order by Salary desc   -- 薪水降序
limit 1 offset 1   -- 从第1行数据之后显示一行：除去最高的薪水之后再显示一行，也就是第二高的薪水

Если в исходных данных есть только одно самое высокое значение, то есть когда нет второй по величине зарплаты, оно должно отображаться как нулевое.Мы используем функцию ifnull для достижения вышеуказанного результата:

select ifnull((select distinct Salary   -- 如果不存在则赋值为null
              from Employee
              order by Salary desc   
              limit 1 offset 1), null) as SecondHighestSalary

Найдите n-й наивысший балл

Теперь разверните тему выше, предполагая наличие двух таблиц: Score и Course, которые можно связать через поле c_id.

Теперь есть требование: узнать второй высший балл и номер ученика по китайскому предмету.

mysql> select * from Score;   -- 成绩表Score：学号s_id、课程号c_id、成绩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)

mysql> select * from Course;   -- 课程表：课程号c_id，课程名c_name，教师编号t_id
+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
| 01   | 语文   | 02   |
| 02   | 数学   | 01   |
| 03   | 英语   | 03   |
+------+--------+------+
3 rows in set (0.00 sec)

Сопоставив две таблицы, мы можем увидеть следующие результаты: мы обнаружили, что самый высокий показатель по китайскому языку составляет 80, а второй по величине — 76, что и является желаемым результатом.

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


select 
	distinct s.s_score  --  分数去重
from Score s 
left join Course c on s.c_id=c.c_id
where c.c_name = '语文'  -- 指定科目
order by s.s_score desc  -- 降序
limit 1 offset 1;  -- limit和offset实现翻页功能

Категории