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

/*
select device_id,
university,
min(gpa) as gpa
from user_profile 
group by university
order by university;
错误：限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的；
程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
SQL_ERROR_INFO: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user_profile.device_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
*/

# <窗口函数> over (partition by <用于分组的列名>
#                 order by <用于排序的列名>)
# 使用窗口函数
select device_id,university,gpa
from 
(select device_id,university,gpa,
   ROW_NUMBER() OVER (partition by university order by gpa )as rk 
 from user_profile 
) as a
where a.rk=1;


# 这是一个求分组内TOPN的问题
with t0 as(
select *, 
row_number() over (partition by university order by gpa asc) as rn 
from user_profile)

select device_id,university,gpa
from t0 
where rn=1;