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

-- join法
select
b.device_id,a.university,a.min_gpa
from (SELECT university,min(gpa) min_gpa from user_profile group by university) a
left join user_profile b on a.university=b.university and a.min_gpa = b.gpa order by university;
-- in法
select device_id,university,gpa 
from user_profile 
where (university,gpa) in (SELECT university,min(gpa) min_gpa from user_profile group by university) order by university;
-- 开窗大法
SELECT device_id, university, min_gpa
FROM (
    SELECT device_id, university, gpa,
           MIN(gpa) OVER (PARTITION BY university ORDER BY university) AS min_gpa
    FROM user_profile
) AS subquery
WHERE gpa = min_gpa;

join最慢，in和开窗差不多