Title: Now the operation wants to find the students with the lowest gpa in each school for research, please take out the lowest gpa of each school.

drop table if exists user_profile;CREATE TABLE `user_profile` (`id` int NOT NULL,`device_id` int NOT NULL,`gender` varchar(14) NOT NULL,`age` int ,`university` varchar(32) NOT NULL,`gpa` float,`active_days_within_30` int ,`question_cnt` int ,`answer_cnt` int);INSERT INTO user_profile VALUES(1,2138,'male',21,'Peking University',3.4,7,2,12);INSERT INTO user_profile VALUES(2,3214,'male',null,'Fudan University',4.0,15,5,25);INSERT INTO user_profile VALUES(3,6543,'female',20,'Peking University',3.2,12,3,30);INSERT INTO user_profile VALUES(4,2315,'female',23,'Zhejiang University',3.6,5,1,2);INSERT INTO user_profile VALUES(5,5432,'male',25,'Shandong University',3.8,20,15,70);INSERT INTO user_profile VALUES(6,2131,'male',28,'Shandong University',3.3,15,7,13);INSERT INTO user_profile VALUES(7,4321,'male',28,'Fudan University',3.6,9,6,52);

Projects

select b.device_id ,a.university,a.gpafrom(select university,min(gpa) as gpafrom user_profilegroup by university)a left join user_profile b on b.university = a.university and b.gpa = a.gpaorder by a.universityselect device_id,university,gpafrom (select*,row_number() over(partition by university order by gpa) as rnfrom user_profile)awhere a.rn = 1