题解 | #找出每个学校GPA最低的同学#

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 asc;

方法2：将表a的device_id,university,gpa和表b的university,min(gpa)连接起来找。

SELECT a.device_id,a.university,a.gpa FROM user_profile a

JOIN (SELECT university,min(gpa) gpa FROM user_profile GROUP BY university) b

on a.university=b.university and a.gpa=b.gpa

ORDER BY university;

方法二：窗口函数

或者

Select device_id,university,gpa from (

Select device_id,university,gpa,

row_number() over (partition by university order by gpa asc) as a From user_profile) t where t.a=1;

有关窗口函数更多请见https://zhuanlan.zhihu.com/p/92654574