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

select
    b.device_id device_id,
    a.university university,
    a.gpa gpa
from
    (
        select
            university,
            min(gpa) as gpa
            # 这里不能取device_id，因为它既没有被聚合函数包裹，又不是group by对象
            # 如果取了可能会导致同一个最低分对应多个id，所有语法上直接不允许
        from
            user_profile
        group by
            1# 表示select的第一个
    ) as a
    left join user_profile as b
    on a.gpa = b.gpa
    and a.university = b.university
order by
    2

select
    device_id,
    university,
    gpa
from
    (
        select
            device_id,
            university,
            gpa,
            row_number() over (
                partition by
                    university
                order by
                    gpa asc
            ) as rn
        from
            user_profile
    ) as x #一定要加别名，标注唯一性，即使没有用到
where
    x.rn = 1
    # 最好加上x.，不加也能跑是因为rn是唯一字段
order by
    2