当前位置:网站首页>每日sql-找到每个学校gpa最低的同学(开窗)
每日sql-找到每个学校gpa最低的同学(开窗)
2022-08-11 05:35:00 【吃再多糖也不长胖】
题目:现在运营想要找到每个学校gpa最低的同学来做调研,请你取出每个学校的最低gpa。
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,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
方案
select b.device_id ,a.university,a.gpa
from
(select university,min(gpa) as gpa
from user_profile
group by university
)a left join user_profile b on b.university = a.university and b.gpa = a.gpa
order by a.university
select device_id,university,gpa
from (
select
*
,row_number() over(partition by university order by gpa) as rn
from user_profile
)a
where a.rn = 1
边栏推荐
- vnc remote desktop installation (available for personal testing on 2021-10-20)
- iptables 流量统计
- pytorch下tensorboard可视化深坑
- 查看内核版本和发行版版本
- Memory debugging tools Electric Fence
- HCIP WPN experiment
- 阿里巴巴规范之POJO类中布尔类型的变量都不要加is前缀详解
- arcgis填坑_3
- My approval of OA project (inquiry & meeting signature)
- HCIP BGP built adjacent experiment
猜你喜欢
随机推荐
导航定位中的坐标系
抖音分享口令url API工具
Open Set Domain Adaptation 开集领域适应
OA项目之我的审批(查询&会议签字)
京东商品详情API调用实例讲解
iptables的状态
自定义MVC增删改查
HCIP MPLS/BGP Comprehensive Experiment
【LeetCode】1036. 逃离大迷宫(思路+题解)压缩矩阵+BFS
MySQL01
HCIP OSPF/MGRE综合实验
CLUSTER DAY01 (Introduction to cluster and LVS, LVS-NAT cluster, LVS-DR cluster)
损失函数——负对数似然
OA项目之会议通知(查询&是否参会&反馈详情)
arcgis填坑_2
MySQL之CRUD
pytorch调整模型学习率
cloudreve使用体验
LabelEncoder和LabelBinarizer的区别
window7开启远程桌面功能