93，SQL训练之，力扣，1412. 查找成绩处于中游的学生

凡梦_leo

于 2024-09-24 02:20:42 发布

阅读量603

点赞数 19

分类专栏： SQL实战训练文章标签： sql 数据库开窗函数排序 pandas leetcode python

本文链接：https://blog.csdn.net/qq_55006020/article/details/142472448

版权

SQL实战训练专栏收录该内容

120 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表: Student

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id 是该表主键(具有唯一值的列)。
student_name 学生名字。

表: Exam

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) 是该表主键(具有唯一值的列的组合)。
学生 student_id 在测验 exam_id 中得分为 score。

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

编写解决方案，找出在所有测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。

返回结果表按照 student_id 排序。

返回结果格式如下。

示例 1：

输入：
Student 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+
Exam 表：
+------------+--------------+-----------+
| exam_id    | student_id   | score     |
+------------+--------------+-----------+
| 10         |     1        |    70     |
| 10         |     2        |    80     |
| 10         |     3        |    90     |
| 20         |     1        |    80     |
| 30         |     1        |    70     |
| 30         |     3        |    80     |
| 30         |     4        |    90     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+
输出：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+
解释：
对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。

三，建表语句

Create table If Not Exists Student (student_id int, student_name varchar(30));
Create table If Not Exists Exam (exam_id int, student_id int, score int);
Truncate table Student;
# insert into Student (student_id, student_name) values ('1', 'Daniel');
# insert into Student (student_id, student_name) values ('2', 'Jade');
# insert into Student (student_id, student_name) values ('3', 'Stella');
# insert into Student (student_id, student_name) values ('4', 'Jonathan');
# insert into Student (student_id, student_name) values ('5', 'Will');
Truncate table Exam;
# insert into Exam (exam_id, student_id, score) values ('10', '1', '70');
# insert into Exam (exam_id, student_id, score) values ('10', '2', '80');
# insert into Exam (exam_id, student_id, score) values ('10', '3', '90');
# insert into Exam (exam_id, student_id, score) values ('20', '1', '80');
# insert into Exam (exam_id, student_id, score) values ('30', '1', '70');
# insert into Exam (exam_id, student_id, score) values ('30', '3', '80');
# insert into Exam (exam_id, student_id, score) values ('30', '4', '90');
# insert into Exam (exam_id, student_id, score) values ('40', '1', '60');
# insert into Exam (exam_id, student_id, score) values ('40', '2', '70');
# insert into Exam (exam_id, student_id, score) values ('40', '4', '80');
# #
# -- 向学生表插入数据
# INSERT INTO student (student_id, student_name) VALUES
# (1, 'Anna'),
# (2, 'Jhon'),
# (3, 'Jade'),
# (4, 'Maria'),
# (5, 'Winston'),
# (6, 'Elvis');
#
# -- 向考试表插入数据
# INSERT INTO exam (exam_id, student_id, score) VALUES
# (10, 2, 30),
# (10, 4, 31),
# (10, 5, 57),
# (20, 2, 43),
# (20, 3, 67),
# (20, 5, 68),
# (20, 6, 70),
# (30, 2, 36),
# (30, 3, 46),
# (40, 1, 46),
# (40, 5, 56),
# (50, 1, 45),
# (50, 2, 55),
# (50, 6, 74);

-- 向学生表插入数据（如果表中已有数据，此部分可以忽略）
INSERT INTO Student (student_id, student_name) VALUES
(1, 'Anna'),
(2, 'Jhon'),
(3, 'Jade'),
(4, 'Maria'),
(5, 'Winston'),
(6, 'Elvis'),
(7, 'Jaze');

-- 向考试表插入数据
INSERT INTO Exam (exam_id, student_id, score) VALUES
(10, 1, 35),
(10, 4, 64),
(10, 5, 68),
(10, 6, 68),
(20, 1, 38),
(20, 2, 52),
(20, 3, 59),
(20, 5, 76),
(20, 7, 84),
(30, 1, 32),
(30, 2, 57),
(30, 5, 66),
(40, 3, 41),
(40, 7, 61),
(50, 5, 34),
(50, 6, 56),
(50, 7, 63),
(60, 2, 53),
(60, 3, 53),
(60, 4, 73),
(60, 5, 73),
(60, 7, 78),
(70, 5, 49);

四，分析

题解:

表1:学生表

字段:学生id,学生姓名

表2:测验表

字段:测验次数,学生id,分数

求

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

编写解决方案，找出在所有测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。

返回结果表按照 student_id 排序。

第一步:合并两个表内连接因为只要参加考试的学生

with t1 as (
    select
        s.student_id, student_name, exam_id, score
    from student s join exam e on s.student_id=e.student_id
)
   select * from t1

第二步:分别开窗排序考虑并列给最大值排序给最小值排序

with t1 as (
    select
        s.student_id, student_name, exam_id, score
    from student s join exam e on s.student_id=e.student_id
)
   ,t2 as (
    select student_id, student_name, exam_id, score,
       row_number() over (partition by student_id) rn
       from t1
),t3 as (
    select student_id, student_name, exam_id, score,
       rank() over (partition by exam_id order by score) rn1,
       rank() over (partition by exam_id order by score desc ) rn2
       from t2 where rn>=1
)
   select * from t3;

第三步:if判断如果rn1 或者rn2 等于1 那么就给他一个值反之在给另外一个值

with t1 as (
    select
        s.student_id, student_name, exam_id, score
    from student s join exam e on s.student_id=e.student_id
)
   ,t2 as (
    select student_id, student_name, exam_id, score,
       row_number() over (partition by student_id) rn
       from t1
),t3 as (
    select student_id, student_name, exam_id, score,
       rank() over (partition by exam_id order by score) rn1,
       rank() over (partition by exam_id order by score desc ) rn2
       from t2 where rn>=1
)
#    select * from t3;
   ,t4 as (
    select
        student_id, student_name, exam_id, score, rn1, rn2,
        if((rn1=1 or rn2=1),0,1) flag
        from t3
)
   select * from t4;

第四步:分别取拆两个表然后在做连接最后去null值的行在去重在排序

with t1 as (
    select
        s.student_id, student_name, exam_id, score
    from student s join exam e on s.student_id=e.student_id
)
   ,t2 as (
    select student_id, student_name, exam_id, score,
       row_number() over (partition by student_id) rn
       from t1
),t3 as (
    select student_id, student_name, exam_id, score,
       rank() over (partition by exam_id order by score) rn1,
       rank() over (partition by exam_id order by score desc ) rn2
       from t2 where rn>=1
)
#    select * from t3;
   ,t4 as (
    select
        student_id, student_name, exam_id, score, rn1, rn2,
        if((rn1=1 or rn2=1),0,1) flag
        from t3
)
#    select * from t4;
   ,t5 as (
    select * from t4 where flag=1
),t6 as (
    select * from t4 where flag=0
),t7 as (
    select distinct t5.student_id,t5.student_name from t5 left join t6 on t5.student_id=t6.student_id
                                                  where t6.student_id is null
                                                  order by t5.student_id
)
select * from t7;

五，SQL解答

with t1 as (
    select
        s.student_id, student_name, exam_id, score
    from student s join exam e on s.student_id=e.student_id
)
   ,t2 as (
    select student_id, student_name, exam_id, score,
       row_number() over (partition by student_id) rn
       from t1
),t3 as (
    select student_id, student_name, exam_id, score,
       rank() over (partition by exam_id order by score) rn1,
       rank() over (partition by exam_id order by score desc ) rn2
       from t2 where rn>=1
)
#    select * from t3;
   ,t4 as (
    select
        student_id, student_name, exam_id, score, rn1, rn2,
        if((rn1=1 or rn2=1),0,1) flag
        from t3
)
#    select * from t4;
   ,t5 as (
    select * from t4 where flag=1
),t6 as (
    select * from t4 where flag=0
),t7 as (
    select distinct t5.student_id,t5.student_name from t5 left join t6 on t5.student_id=t6.student_id
                                                  where t6.student_id is null
                                                  order by t5.student_id
)
select * from t7;