题解 | #找出每个学校GPA最低的同学#
找出每个学校GPA最低的同学
https://www.nowcoder.com/practice/90778f5ab7d64d35a40dc1095ff79065
两种经典的做法,一种属于where的高级应用,另一种是from子查询+排序窗口函数的结合应用。
1.
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
where (university,gpa) in
(select university,min(gpa) from user_profile
group by university)
order by university
2.
select device_id,university,gpa from
(select device_id,university,gpa,rank()over(partition by university order by gpa) 排名 from user_profile
group by university,device_id,gpa) as rk
where rk.排名 = 1
order by university
(select device_id,university,gpa,rank()over(partition by university order by gpa) 排名 from user_profile
group by university,device_id,gpa) as rk
where rk.排名 = 1
order by university