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

# # with子句 + 一步步拆解/
# with
#     temp as(
#         #对每个学校的gpa排序
#         select distinct
#             device_id,
#             university,
#             row_number() over(partition by university order by gpa) as ranking,
#             min(gpa) over(partition by university order by gpa) as gpa
#         from user_profile
#         order by university
#     )
# #取出每个学校gpa最低的同学的device_id
# select 
#     device_id,
#     university,
#     gpa
# from temp
# where ranking = 1

# # 表连接
# select
#     u.device_id,
#     u.university,
#     u.gpa
# from user_profile u
# join(
#     #统计每个学校的最低gpa
#     select
#         university,
#         min(gpa) as gpa
#     from user_profile
#     group by university
#     ) as attr on u.university = attr.university and u.gpa = attr.gpa
# order by university

# # where子句
# select
#     device_id,
#     university,
#     gpa
# from user_profile
# where (university,gpa) in(
#     #统计每个学校的最低gpa
#     select
#         university,
#         min(gpa) as gpa
#     from user_profile
#     group by university
# )
# order by university


select device_id,university,gpa
from
(select *,rank() over (partition by university order by gpa) as rk 
from user_profile) a
where a.rk=1
order by university;

"(select *,rank() over (partition by university order by gpa) as rk

from user_profile)" 为临时创建的派生表，后面必须附带有唯一标识的表名，例如 a；

rank()/row_number()/dense_rank()/min()/sum()... over(partition by xx order by xxx)