窗口函数 row_number() over partition by
函数的含义为先分组再排序, row_number() over (partition by col1 order by col2),
表示根据col1分组,在分组内部根据col2排序,为了更好的理解,我们来看个例 子
select device_id, university,gpa,
row_number() over (partition by university order by gpa desc) as rank -- desc代表降序排列
from user_profile
上述代码含义为根据大学分组,在每个组内部即每个学校的内部根据gpa进行一次排名,获得每个学生在学校的名次数据,填入rank列,desc代表是按照从大到小降序排列。
本题解答
首先对group by理解不透彻写了下述错误代码
SELECT
device_id,
university,
min(gpa) as gpa
FROM user_profile
GROUP BY university
因为学校与学生是一对多的关系,如果仅用min求出gpa最低的学生,查询结果中的id与学生不一定是对应的关系,因此此方法错误。不理解的可以查看(3条消息) mysql group by详解_愈努力俞幸运的博客-CSDN博客https://blog.csdn.net/qq_37891604/article/details/127428724 也就是说,分组后,表格里的内容需要你去选择自己需要的,不然不知道蹦出啥。
第一种方法
select
device_id,
university,
gpa
from user_profile
where (university,gpa) in (select
university,
min(gpa)
from user_profile
group by university)
order by university
select a.device_id, a.university, a.gpa from user_profile a join (select university, min(gpa) as gpa from user_profile group by university) b ON a.university=b.university and a.gpa=b.gpa order by a.university
一般能用in的也可以用join
窗口函数
select device_id, university, gpa
from (
select *,
row_number() over (partition by university order by gpa) as rn
from user_profile
) as univ_min
where rn=1
order by university