首页 > 试题广场 >

找出每个学校GPA最低的同学

[编程题]找出每个学校GPA最低的同学

热度指数：432835 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

示例：user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

根据示例，你的查询结果应参考以下格式，输出结果按university升序排序：

device_id	university	gpa
6543	北京大学	3.2
4321	复旦大学	3.6
2131	山东大学	3.3
2315	浙江大学	3.6

示例1

输入

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);

输出

device_id|university|gpa
6543|北京大学|3.2
4321|复旦大学|3.6
2131|山东大学|3.3
2315|浙江大学|3.6

算法知识视频讲解

Mysql

牛客872544921号

不用group by 也能做！

利用相关子查询，把每个学校的最低gpa当作查询条件，去找出每个学校的gpa最低的同学。因为每个学校只有一个gpa最低的同学，所以最后出来的结果不需要再用group by，用order by排序就好。看代码！！

在子查询中，我们利用到了主查询的表，WHERE university = u.university 这个条件使得mysql去主表得每一行进行查询，比如第一行是2138的北京大学的同学，那么子查询会找出所有北京大学的同学，并且找出其中最低得gpa，如果他是最低的那个就留下，不是就下一个。以此类推找出所有大学的最低gpa的同学，最后排序得最终结果。

小白一个😅，如果觉得解释不清楚可以去B站看看这个相关子查询的视频【中字】SQL进阶教程 | 史上最易懂SQL教程！的P51，讲的很清晰易懂。

SELECT
    device_id,
    university,
    gpa
FROM user_profile u
WHERE gpa = 
    (SELECT MIN(gpa)
     FROM user_profile
     WHERE university = u.university)
ORDER BY university

发表于 2021-09-29 23:15:28 回复(21)

Mysql

其实是牛哥

窗口函数的含义为先分组再排序, row_number() over (partition by col1 order by

col2),表示根据col1分组，在分组内部根据col2排序。

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
)a
where rk = 1

编辑于 2021-08-26 12:27:36 回复(34)

Mysql

牛客342783178号

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

发表于 2021-09-02 17:22:03 回复(12)

阳向花开

1.每个学校，所以需要按学校分组 group by university

2.最低使用min函数，直接用min查出来的device_id和gpa对应不上。想到用子查询，先分组学校查出每个学校的最低gpa，再自联结根据条件查出对应数据行，注意on的条件里device_id没什么用，因为子程序里的device_id和min（gpa）不对应

3.按要求排序学校返回

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

发表于 2021-08-30 22:49:32 回复(24)

Jormungand·

SELECT device_id, university, gpa
FROM user_profile
WHERE gpa IN( # IN 可替换为 = ANY
    SELECT min(gpa)
    FROM user_profile
    GROUP BY university
)
GROUP BY university # 保证学校名不重复
ORDER BY university; # 保证与题目要求输出顺序一致

发表于 2021-09-02 16:18:58 回复(10)

凌12

题目中有两点需要注意：

1.device_id是最小值gpa对应的，不能直接使用select device_id,university,min（gpa from user_profile group by university；

2.题目中没有要求用university进行正序排序，但是如果不加的话不会通过；

我不知道是不是题目修改了之前提交成功的，现在不行了（已做修改）

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

编辑于 2022-03-11 09:34:15 回复(11)

奋斗终身_伟大复兴

# 主要还是学校跟学生是一对多的关系会导致device_id对应问题
# select device_id,university,min(gpa) from user_profile
# 使用上面一条sql，就可以清楚知道，对应关系问题的产生！！！
#我用的是内连接，大家的方法思路都差不多，将单表分为两个表，再将两个表进行连接进行查询!!!
SELECT u.device_id,u.university,u.gpa # 需要的数据字段
FROM
user_profile u # 原表
JOIN
(SELECT university,MIN(gpa) as gpa from user_profile GROUP BY
university) as newu #新查询出来的新表
ON
u.university=newu.university #两个连接条件，进行连接后，得到device_id
and u.gpa=newu.gpa
ORDER BY
u.university; # 进行默认升序处理，得到同样的顺序结果

发表于 2021-09-24 11:58:49 回复(4)

牛客541680488号

SELECT
	device_id,
	university,
	gpa 
FROM
	user_profile u 
WHERE
	gpa = ( SELECT MIN( gpa ) FROM user_profile WHERE university = u.university ) 
ORDER BY
	university

发表于 2021-11-12 23:35:24 回复(3)

Mysql

酸菜鱼土豆大侠

【场景】：每个--最低（高）--
【分类】：窗口函数、表连接、分组查询

分析思路

难点：
1.如何取出使用分组函数之后得不到的device_id？连接其他表或者使用排序函数

第一眼看是分组查询，实际上是窗口函数！

（1）统计每个学校的最低gpa

[使用]：min

（2）取出每个学校gpa最低的同学的device_id

[使用]：join

最终结果

select 查询结果 [用户号;学校;gpa]
from 从哪张表中查询数据[多表]
order by 对查询结果排序 [学校];

求解代码

方法一：

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

方法三：

子查询

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

发表于 2022-11-27 11:55:43 回复(1)

Mysql

在许愿的独行侠很愉快

select device_id, t2.university, gpa
from 
user_profile t1,
(select university, min(gpa) as min_gpa 
from user_profile
group by university) t2
where t1.university = t2.university
and t1.gpa = t2.min_gpa
order by university

这个题还是挺难的吧！

不能直接查询，最低使用min函数，直接用min查出来的device_id和gpa对应不上，所以使用子查询

思路如下：

1.看到学校，想到用学校分组，min(gpa)查询每个学校的最低成绩

2.可以先写一个子查询，将查询结果当个临时表使用

3.进行多表查询，用where进行过滤，条件为学校，最低成绩

发表于 2021-10-30 16:33:16 回复(8)

Mysql

许小北_

有的小伙伴可能不理解为什么不能用min(gpa),稍微解释一下.

本题难点:device_id对应的是个体的值,但是university(也可以看成个体的值)和gpa是分组后对应的值!

下面这段代码是正确的,输出结果也和答案一样,但是如果在select中再加入device_id就会报错,原因在上面粗体.

SELECT university,min(gpa) gpa
FROM user_profile
GROUP BY university
ORDER BY gpa;

那么,如何把个体的值和分组计算的结果对应在一起呢?(忽然觉得python里的transform甚是好用啊!)

既然,聚合函数这条路走不通,我们就换一条思路.

新思路:按照university对数据集进行分组,对组内数据进行排序,按照gpa进行升序排列,此时有几个学校就会有几个序号为1的记录,这几条记录就是我们需要的结果.

沿着这个思路我们自然而然就会想到窗口函数,利用窗口函数生成一个行的列,该列表示了各组组内对于某个变量的排序结果.

需要注意的是,实际中每个学校的最低gpa可能有多个结果,所以窗口函数选择rank()更合适,但由于本题中每个学校只显示一条记录,所以这里用的是row-numb().

select device_id,university,gpa
FROM(
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY university ORDER BY gpa ASC) rn
    FROM user_profile
) wt
WHERE rn=1
ORDER BY university;

发表于 2024-08-29 16:59:32 回复(2)

Mysql

刮胡刀卡在下巴里

SELECT device_id,university,MIN(gpa) AS gpa FROM user_profile
GROUP BY university;

在MySQL8.0中就能运行出正确结果呢,在这里提交就不行,奇怪

发表于 2022-03-08 23:44:17 回复(6)

Mysql

牛客677475127号

uu们，好无语呀！顺序跟答案不一样也算错吗？

发表于 2021-10-01 17:39:08 回复(3)

殇ぁ

select device_id, university, gpa from user_profile where (university, gpa) in (
select university, min(gpa) from user_profile
group by university

)

这竟然不过，我不能接受=-=

发表于 2021-08-29 18:33:43 回复(9)

Mysql

牛客237336299号

select device_id,university,gpa
from (SELECT device_id,university,gpa
,rank()over(partition by university order by gpa) r
from user_profile) a
where r=1
order by university

发表于 2022-02-13 18:08:11 回复(0)

Mysql

牛客司机

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
  ) tab
where
  rk = 1

发表于 2022-06-05 21:49:04 回复(6)

Mysql

luluer

请教一下各位大佬为什么我这个code会比答案多出一行呢

select device_id, 
university,
gpa
from user_profile
where gpa in (
select min(gpa)
from user_profile
group by university)
order by university

发表于 2022-09-06 21:33:31 回复(7)

细致的大老虎在努力

group by university会丢失学生id;
group by university，device_id 是按每个学校每个学生分组，就没办法取每个学校的min值了；
所以如果用group by 需要嵌套；

发表于 2023-03-01 10:44:32 回复(0)

Mysql

李玉婷的旁听生

窗口函数的结果是不是更准确一些？有点迷惑

-- 直接窗口函数（可以得到最低的多个并列值）
select device_id,university,
min(gpa)over(partition by university) as m_gpa
from user_profile
order by university desc;

-- 内连接之后，仅1个并列值
select u.device_id,u.university,u.gpa
from user_profile u,(select u.university,min(gpa) as m_gpa
	from user_profile u
	group by university) t
where t.university=u.university and t.m_gpa=u.gpa
order by u.university;

发表于 2022-07-24 21:17:07 回复(1)

Mysql

lun__

# 方法 1 
# 使用 row_number() over 完成 行号的取出  
select 
    device_id, 
    university, 
    gpa 
from  
    (select  
         *, 
        row_number() over (partition by university order by gpa)as r
     from  
         user_profile 
    ) as tmp 
where 
    r = 1
;


# 方法 2  
## 使用 where in 选出小表中的特定数据 
select 
    device_id, 
    university, 
    gpa 
from  
    user_profile
where 
    (university, gpa) 
    in (
        select 
            university, 
            min(gpa) as gpa 
        from 
            user_profile 
        group by 
            university 
    ) 
order by 
    university
; 


## 方法 3 
### 通过 join 表 on 条件 完成大表和小表的拼接，然后选出对应的列 
select 
    u.device_id, 
    u.university, 
    u.gpa 
from 
    user_profile as u 
    join  (
        select 
            university, 
            min(gpa) as gpa 
        from  
            user_profile  
        group by 
            university 
    ) as l  
    on u.gpa = l.gpa and u.university = l.university 
order by 
    u.university 
; 


# 方法 3 
# where u.gpa = () order by 完成大表和小表的筛选 
select 
    u.device_id, 
    u.university, 
    u.gpa 
from 
    user_profile as u 
where u.gpa = 
    ( 
        select 
            min(gpa) as gpa 
        from 
            user_profile as uu 
        where 
            uu.university = u.university 
        group by 
            uu.university 
    )
order by 
    u.university  
;

发表于 2022-05-29 10:45:28 回复(0)