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

# "分组取最值" (Group-wise Maximum/Minimum) 或者 "Top-N 问题" (这里是 Top-1)
# 使用1. Where 子查询 (非相关子查询)
# 思路：先算出每个学校的最低 GPA 是多少，然后回原表去查“谁的 GPA 等于这个学校的最低 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;
# 2. 自连接 (或者叫 Inner Join 临时表)
# select u1.device_id,u1.university,u1.gpa
# from user_profile as u1
#     join 
#         (select university,min(gpa) as gpa 
#         from user_profile 
#         group by university) as u2
#     on u1.university = u2.university and u1.gpa = u2.gpa
# order by u1.university
select device_id,university,gpa
from(
    SELECT *,
    RANK() OVER (PARTITION BY university ORDER BY gpa ASC) as rk
    FROM user_profile
) as t
where t.rk = 1
order by university