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

select 
    device_id,
    university,
    gpa
from
    (select 
        device_id,
        university,
        gpa,
        row_number() over(partition by university order by gpa) as ranking
    from user_profile) as ranking_t
where ranking = 1

mysql窗口函数：

https://zhuanlan.zhihu.com/p/525367009

先给原始表对gpa排序

select

device_id,

university,

gpa,

row_number() over(partition by university order by gpa) as ranking

from user_profile

再对排序的表取最低，得到结果

select

device_id,

university,

gpa

from

(select

device_id,

university,

gpa,

row_number() over(partition by university order by gpa) as ranking

from user_profile) as ranking_t

where ranking = 1