题解 | 找出每个学校GPA最低的同学
找出每个学校GPA最低的同学
https://www.nowcoder.com/practice/90778f5ab7d64d35a40dc1095ff79065
#思路一:利用表的左连接,首先建立临时表:找到每个学校的最低gpa;再将临时表作为主表,与user_profile进行连接,条件控制学校和gpa,以找到其对应的device_id。 with a as ( select distinct university, min(gpa) as gpa from user_profile group by 1 ) select b.device_id, a.university, a.gpa from a left join ( select * from user_profile )b on b.gpa = a.gpa and b.university = a.university order by a.university ; #思路二:利用窗口函数,over(partition by university order by gpa) with a as ( select device_id, university, gpa, row_number()over(partition by university order by gpa) as rk from user_profile ) select device_id, university, gpa from a where a.rk = 1 ;