SQL题——找出每个学校GPA最低的同学

  • 0
  • 0
  • 701

描述

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。
 
示例：user_profile
id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male   复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 female 26 复旦大学 3.6 9 6 52
根据示例，你的查询结果应参考以下格式，输出结果按university升序排序：
device_id university gpa
6543 北京大学 3.2000
4321 复旦大学 3.6000
2131 山东大学 3.3000
2315 浙江大学 3.6000

示例1

输入： 
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
输出： 
6543|北京大学|3.200
4321|复旦大学|3.600
2131|山东大学|3.300
2315|浙江大学|3.600

 

题意明确：

每个学校里gpa最低的同学

问题分解：

  • 限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的；
  • 每个学校最低：
    • 第一种方式是用group by把学校分组，然后计算得到每个学校最低gpa，再去找这个学校里和这个gpa相等的同学ID。注意这样如果最低gpa对应多个同学，都会输出，题目没有明确此种情况，心理明白就行。
    • 第二种方式是利用窗口函数，先按学校分组计算排序gpa，得到最低gpa的记录在用子查询语法拿到需要的列即可。此题中rou_number可以得到排序后的位序，取位序为1即可得到最小值（升序时）。
  • 窗口函数语法：row_number/rank/dense_rank over (partition by FIELD1 order by FIELD2)，传送链接

细节问题：

  • 如果题目明确了有多个最低gpa情况下，输出结果情况，需要留意
  • 表头重命名：as

完整代码：

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select a.device_id, a.university, a.gpa
from user_profile a
right join
(
    select university, min(gpa) as gpa
    from user_profile
    group by university
) as b
on a.university=b.university and a.gpa=b.gpa
order by a.university
 
-- 方法二：
select device_id, university, gpa
from (
    select *,
    row_number() over (partition by university order by gpa) as rn
    from user_profile
) as univ_min
where rn=1
order by university
其它：
  select device_id,university,gpa
from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university)
order by university​

注意：本文归作者所有，未经作者允许，不得转载

点赞 0
#Sql
阅读全部

已有 0 条评论

    欢迎您，新朋友，感谢参与互动！