首页 > 试题广场 >

得分不小于平均分的最低分

[编程题]得分不小于平均分的最低分

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

请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。

示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1002	9001	2021-09-05 19:01:01	2021-09-05 19:40:01	89
3	1002	9002	2021-09-02 12:01:01	(NULL)	(NULL)
4	1002	9003	2021-09-01 12:01:01	(NULL)	(NULL)
5	1002	9001	2021-02-02 19:01:01	2021-02-02 19:30:01	87
6	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90
7	1003	9002	2021-02-06 12:01:01	(NULL)	(NULL)
8	1003	9003	2021-09-07 10:01:01	2021-09-07 10:31:01	86
9	1004	9003	2021-09-06 12:01:01	(NULL)	(NULL)

examination_info表（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	SQL	easy	60	2020-02-01 10:00:00
3	9003	算法	medium	80	2020-08-02 10:00:00

示例输出数据：

min_score_over_avg

解释：试卷9001和9002为SQL类别，作答这两份试卷的得分有[80,89,87,90]，平均分为86.5，不小于平均分的最小分数为87

示例1

输入

drop table if exists examination_info;
CREATE TABLE  examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE  exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9002, '2021-02-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null);

输出

备注:

保证至少有一个有效的SQL类别的试卷作答分数

算法知识视频讲解

Mysql

骨碌圆

-- 方法1:用窗口函数直接求出avg，作为一列。然后score与avg比较
select min(t.score) min_score_over_avg from
(SELECT er.score,
avg(er.score) over() avg_score from exam_record er
left join examination_info ei
on er.exam_id=ei.exam_id
where ei.tag='SQL'
and er.score is not null)t
where t.score>=t.avg_score; 

-- 方法2：聚合函数求出平均值，作为表中的一个筛选条件
SELECT min(er.score) min_score_over_avg  from exam_record er
left join examination_info ei
on er.exam_id=ei.exam_id
where ei.tag='SQL'
and er.score>=
(SELECT avg(er.score) from exam_record er
left join examination_info ei
on er.exam_id=ei.exam_id
where ei.tag='SQL');

发表于 2021-10-21 13:36:52 回复(13)

Mysql

苏打烟花无糖粉

这里的对比符号记得要是 >= ，题目要求得分不小于平均分的最低分，不然提交的结果会NONE

with t1 as(
    SELECT er.*,ei.tag
    FROM exam_record er INNER JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    WHERE tag = "SQL" AND submit_time is not null
)


# 子查询查询SQL的平均分
SELECT min(score)
FROM t1
WHERE score >= (SELECT avg(score)
               FROM t1)

发表于 2022-02-25 12:00:54 回复(4)

Mysql

啊坡糍嘚

select min(t.score) as min_score_over_avg
from 
(select t1.exam_id,if(t1.score is null,0,t1.score) as score,t2.tag
 ,avg(score)over(partition by t2.tag) as avg_score
 from exam_record t1 left join examination_info t2 on t1.exam_id=t2.exam_id
) t 
 where t.score>=t.avg_score and t.tag='SQL'

1.先利用聚合窗口函数avg()建立一张临时表t,该表包含了每个试卷类型的平均分；

2.找出满足试卷类型为sql和分数大于等于平均分的记录；

3.查询上述记录里的最小分数即为所求答案。

（牛客测试系统真的有点迷，昨天这个代码怎么试都有一组例子不过，今天就全能通过了。搞不懂~）

编辑于 2021-11-03 22:04:37 回复(12)

Mysql

Tina要成为数据分析师呀

select min(er.score) as min_score_over_avg from exam_record er
    join examination_info e
    on er.exam_id=e.exam_id
    where e.tag='SQL'
    and er.score>=(select avg(er.score) from exam_record er 
                  join examination_info e
                  on er.exam_id=e.exam_id
                  where e.tag='SQL')

发表于 2022-01-04 15:52:36 回复(0)

Mysql

酸菜鱼土豆大侠

【场景】：某个条件下做统计

【分类】：where 数据 > select 条件

分析思路

难点：

1.理解题目要求的是什么

（1）得到SQL试卷的平均分

[条件]：where tag = 'SQL'
[条件]：avg(score)

（2）找到得分不小于平均分的最低分

[条件]：score >= score_avg
[使用]：min(score) 或者正序取第一条数据

求解代码

方法一：

with子句

with
    main as(
        #得到SQL试卷的平均分
        select
            avg(score) as score_avg
        from exam_record a, examination_info b
        where tag = 'SQL'
        and a.exam_id = b.exam_id
    )
#找到得分不小于平均分的最低分
select 
    min(score) as min_score_over_avg
from exam_record a, examination_info b,main
where b.tag = 'SQL'
and a.exam_id = b.exam_id
and score >= score_avg

方法二：

where select

select 
    min(score) as min_score_over_avg
from exam_record a, examination_info b
where b.tag = 'SQL'
and a.exam_id = b.exam_id
and score >= (
    select
        avg(score) as score
    from exam_record a, examination_info b
    where b.tag = 'SQL'
    and a.exam_id = b.exam_id
)

方法三：

order by limit

select 
    score as min_score_over_avg
from exam_record a, examination_info b
where b.tag = 'SQL'
and a.exam_id = b.exam_id
and score >= (
    select
        avg(score) as score
    from exam_record a, examination_info b
    where b.tag = 'SQL'
    and a.exam_id = b.exam_id
)
order by score
limit 1

发表于 2022-11-23 17:42:54 回复(1)

牛牛学SQL

为什么我写的代码在自测运行的时候是通过的，但是保存提交就显示错误了呢？自测运行结果是87，保存提交变成了NONE

SELECT

MIN(score) AS min_score

FROM exam_record

WHERE exam_id in

(SELECT exam_id FROM examination_info WHERE tag = 'SQL')

AND score >= (SELECT Avg(score) FROM exam_record AS a LEFT JOIN examination_info AS b ON a.exam_id = b.exam_id and tag = 'SQL')

发表于 2022-12-08 18:02:35 回复(0)

Mysql

冷凡社长

解题点求出SQL试题的平均分，然后让分数与其比较，然后找出最小值。

思路点，知道子查询可以用在where 中的运算判断中。

with t2 as 
(select t.* 
from exam_record t join examination_info t1 using(exam_id)
where tag = "SQL")

select min(score) from t2 where score >= (select avg(score) from t2)

自己最开始想到的是利用select 子查询，做出来后觉得不够简洁。

with t2 as 
(select t.* 
from exam_record t join examination_info t1 using(exam_id)
where tag = "SQL")

select min(score) from (
select  *,(select avg(score) from t2) as avg_1  from t2)t3
where score >=avg_1

with 是临时表，句式位 with 表名 as (查询)
这样这个表名就可以复用了。

窗口函数方式：
略

发表于 2022-08-18 14:43:49 回复(3)

Mysql

凌12

首先得看清楚题目，求的是tag='SQL'的平均值以及在tag='SQL'中的得分不小于平均分的最低分😂

第一步：求出tag='SQL'的平均分

select avg(score)from exam_record join examination_info on examination_info.exam_id=exam_record.exam_id
where tag='SQL'

第二步：求最低分。可以使用min()函数，也可以排序后取值

a.select min(score)
b.order by score limit 1

然后就是进行两个表的联合查询出结果即可：

select score  from exam_record
join examination_info
on examination_info.exam_id=exam_record.exam_id
where score>=(
select avg(score)from exam_record join examination_info on examination_info.exam_id=exam_record.exam_id
where tag='SQL')
and tag='SQL'
order by score limit 1

发表于 2022-03-25 15:39:07 回复(1)

Mysql

键盘盘盘盘

select
    min(er.score) min_score_avg 
from 
    exam_record er
left join 
    examination_info ei
using
    (exam_id)
where 
    ei.tag = 'SQL'
and
    er.score >= (
          select
              avg(er.score)
          from
              exam_record er
          left join
              examination_info ei
          using
              (exam_id)
          where
              ei.tag = 'SQL'
    )

多做多练！

发表于 2021-12-16 17:39:00 回复(3)

Mysql

ZHMMM

SELECT
MIN(score) AS min_score_over_avg
FROM exam_record er
JOIN examination_info ei USING(exam_id)
WHERE ei.tag = 'SQL' AND
score >= (SELECT AVG(score)
FROM exam_record
JOIN examination_info USING(exam_id)
WHERE tag = 'SQL')

发表于 2021-10-26 16:28:09 回复(3)

Mysql

Esthan

with t as(select uid,a.exam_id,score,tag from exam_record a left join examination_info b
using(exam_id) where tag = 'SQL')

select min(score) min_score_over_avg from t where
score >= (select avg(score) from t group by tag)

发表于 2022-07-27 11:15:37 回复(0)

牛客876728443号

SELECT MIN(score) AS min_score_over_abg
FROM exam_record LEFT JOIN examination_info USING(exam_id)
WHERE tag = 'SQL' AND score IS NOT NULL 
AND score >= (SELECT AVG(score) FROM exam_record WHERE tag = 'SQL')

发表于 2022-03-22 21:08:46 回复(1)

趣多多天敌

select min(t.score) as min_score_over_avg from
(
    select er.score,
    avg(er.score) over() as avg_score
    from exam_record er , examination_info ei
    where ei.tag = "SQL" and ei.exam_id = er.exam_id and er.score is not null)t
where t.score >= t.avg_score

发表于 2022-03-08 16:06:37 回复(0)

牛客863583705号

select min(score) min_score_over_avg from exam_record left join examination_info ei on exam_record.exam_id = ei.exam_id
where tag = 'SQL'
and score > (select avg(score) from exam_record left join examination_info ei on exam_record.exam_id = ei.exam_id
where tag = 'SQL');

发表于 2022-02-28 18:23:09 回复(0)

柒鳴飞

select score min_score_over_avg 
from exam_record er 
left join examination_info ei on er.exam_id = ei.exam_id
where tag = 'SQL' 
and score >= (select avg(score) from exam_record er
              left join examination_info ei 
              on er.exam_id = ei.exam_id where tag = 'SQL')
order by min_score_over_avg 
limit 1

-----------------------------

select min(score) min_score_over_avg 
from exam_record er 
left join examination_info ei on er.exam_id = ei.exam_id
where tag = 'SQL' 
and score >= (select avg(score) from exam_record er
              left join examination_info ei 
              on er.exam_id = ei.exam_id where tag = 'SQL')

发表于 2021-12-15 15:50:18 回复(0)

Mysql

木子七月瑾

with ftab as(
select er.*,ei.tag
FROM exam_record er
left join examination_info ei
on ei.exam_id=er.exam_id
where tag='SQL')

select min(score) FROM ftab
where score >= (select avg(score) from ftab)

发表于 2021-12-12 20:00:25 回复(0)

Mysql

爱吃桃桃桃桃


	
	
		select 
	

	
		min(score) as min_score_over_avg
	

	
		from exam_record a join examination_info b on a.exam_id = b.exam_id
	

	
		where b.tag = 'SQL' 
	

	
		and score >=(
	

	
		            select avg(score)
	

	
		            from exam_record a join examination_info b on a.exam_id = 
	

	
		            b.exam_id
	

	
		            where tag = 'SQL'
	

	
		)

编辑于 2024-04-24 10:36:36 回复(1)

Mysql

dolen

# 请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。

with dws_data as (
select 
uid,
t1.exam_id,
score, 
avg(score)over() avg_score -- 平均得分
from exam_record t1 
inner join examination_info t2
on t1.exam_id = t2.exam_id
where submit_time is not null and tag = 'SQL'
) 

select 
min(score) as min_score
from dws_data
where score >= avg_score;

发表于 2023-09-05 10:44:42 回复(0)

Mysql

安东尼亚

with s as (
    select score
    from exam_record left join examination_info using(exam_id)
    where tag = 'SQL' and score is not null
)

select min(score) min_score_over_avg 
from s
where score >= (select avg(score) from s)

发表于 2023-08-28 14:14:38 回复(0)

不愿再收感谢信的鳄鱼很失落

返回的是分数的话直接用orderby+limit

with t1 as (select e1.*from exam_record e1,examination_info e2 where e1.exam_id=e2.exam_id and tag='SQL' and score is not null)

select t1.score min_score_over_avg from t1 where score >= (select avg(t1.score) from t1) order by score limit 1

假如需要返回是id 则需要用开窗函数解决分数相同的问题

with t1 as (select e1.*from exam_record e1,examination_info e2 where e1.exam_id=e2.exam_id and tag='SQL' and score is not null)

select id from 
(select id,t1.score min_score_over_avg,dense_rank() over(order by score) minscore from t1 where score >= (select avg(t1.score) from t1) ) t2
where minscore=1

发表于 2023-07-19 17:29:56 回复(0)