sql常用函数-窗口函数

窗口函数详细语法：https://zhuanlan.zhihu.com/p/92654574
题目：https://www.nowcoder.com/practice/90778f5ab7d64d35a40dc1095ff79065?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1：使用窗口函数row_number：

select
    device_id, university, gpa
from 
    (
     select
         *,
         row_number() over (
             partition by university 
             order by gpa asc
         ) as ranking
    from 
         user_profile
    ) as ranking_user_profile
where 
    ranking_user_profile.ranking = 1
order by 
    university asc;

法2：

select 
    device_id, t1.university, t1.gpa
from 
    user_profile as t1
#     join # 本题,这里是join 和 right join都对。 left join错
    right join
    (
    select 
        university, min(gpa) as gpa
    from 
        user_profile # 这儿只能些表的原名。写为t1的话报错："Table 't1' doesn't exist"
    group by 
        university
    ) as t2
    on 
    t1.university = t2.university and t1.gpa = t2.gpa
order by 
    t1.university asc;