首页 > 试题广场 >

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

[编程题]得分不小于平均分的最低分
  • 热度指数：194842 时间限制：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
87

解释：试卷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);

输出

87

备注:
保证至少有一个有效的SQL类别的试卷作答分数
算法知识视频讲解
添加回答
Mysql
米熬头像 米熬
发表于 2025-07-07 09:38:56 回复(0)
Mysql
摇光Alkaidlc头像 摇光Alkaidlc 
SELECT
    MIN(er.score) AS min_score_over_avg
FROM
    exam_record er, examination_info ei
WHERE
    score >= (
        SELECT
            AVG(er1.score)
        FROM
            exam_record er1
            JOIN examination_info ei1 ON(er1.exam_id = ei1.exam_id)
        WHERE
            tag = 'SQL'
    ) AND ei.tag = 'SQL'
为什么自测没问题，提交就
程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
SQL_ERROR_INFO: "Unknown column 'uid' in 'field list'"。。？
发表于 2025-06-07 19:26:29 回复(0)
Mysql
等offer的萝卜头像 等offer的萝卜
SELECT MIN(score) AS min_score_over_avg
FROM exam_record a JOIN examination_info b
ON a.exam_id = b.exam_id
WHERE tag = 'SQL' AND score >= (
    SELECT AVG(score) AS avg_score
    FROM exam_record a JOIN examination_info b
    ON a.exam_id = b.exam_id
    WHERE tag = 'SQL' AND score IS NOT NULL
);
发表于 2025-03-31 21:44:12 回复(0)
Mysql
接受所有小饼干头像 接受所有小饼干
方法一：聚合函数+表连接
with a as(
select
exam_id,
avg(score) as avg_score
from exam_record
group by exam_id
)

select
min(score) as min_score_over_avg
from (
    select
    exam_record.exam_id,
    score,
    avg_score
    from exam_record
    left join a
    on exam_record.exam_id = a.exam_id
    left join examination_info
    on exam_record.exam_id = examination_info.exam_id
    where tag='SQL'
) b
where score >= avg_score
方法二：窗口函数
select
min(score) as min_score_over_avg
from (
    select
    exam_record.exam_id,
    score,
    avg(score)over() as avg_score
    from exam_record
    left join examination_info
    on exam_record.exam_id = examination_info.exam_id
    where tag='SQL'
) a
where score >= avg_score

发表于 2025-03-08 16:41:39 回复(0)
Mysql
牛客775456359号头像 牛客775456359号

感觉自己总是越做越复杂，离简洁的答案越行越远
select
    score min_score_over_avg
from
    (
        select
            score,
            row_number() over (
                order by
                    score
            ) num
        from
            exam_record er
            left join examination_info ei on er.exam_id = ei.exam_id
        where
            tag = 'SQL'
            and score >= (
                select
                    avg(score) avg_score
                from
                    exam_record er
                    left join examination_info ei on er.exam_id = ei.exam_id
                where
                    tag = 'SQL'
            )
    ) t1
where
    num = 1
发表于 2025-02-28 20:49:33 回复(0)
Mysql
安静的牛油果不服输头像 安静的牛油果不服输 
select min(x.score) min_score_over_avg
from
(
select score#avg(score)
from examination_info
join exam_record on examination_info.exam_id=exam_record.exam_id
where tag='SQL' and score is not null
) x
where score>=
(
select avg(score)
from examination_info
join exam_record on examination_info.exam_id=exam_record.exam_id
where tag='SQL' and score is not null
) 

多次报SQL_ERROR_INFO: "Unknown column 'uid' in 'field list'"，我的解决方式是使用
on examination_info.exam_id=exam_record.exam_id 来连接表，如果用using(exam_id) 就会错，求解
发表于 2025-02-25 19:56:27 回复(0)
Mysql
在抱佛脚的柯基头像 在抱佛脚的柯基
SELECT p.`score` FROM `exam_record` p
LEFT JOIN `examination_info` q USING (`exam_id`)
WHERE q.`tag`= 'SQL' AND p.`score` IS NOT NULL
    AND p.`score` >= (SELECT AVG(p2.`score`) FROM `exam_record` p2  LEFT JOIN  `examination_info` q2 USING (`exam_id`) WHERE q2.`tag`= 'SQL' AND p2.`score` IS NOT NULL )
ORDER BY p.`score`
LIMIT 1;
发表于 2024-12-05 00:00:26 回复(0)
Mysql
平凡上班族11头像 平凡上班族11
select
min(score)min_score_over_avg
from exam_record
where score>=
(
    select
    avg(score)
    from exam_record
    join examination_info
    on exam_record.exam_id=examination_info.exam_id
    where tag='SQL'
)
这个语句是错在哪儿呢，怎么不可以通过
发表于 2024-10-20 13:13:05 回复(0)
Mysql
牛客987852806号头像 牛客987852806号
这题有毛病了吧。Unknown column 'uid' in 'field list 都没用uid...
发表于 2024-08-26 10:51:18 回复(6)
Mysql
liudelantu头像 liudelantu 
-- 分析
-- WHERE:    SQL试卷
-- 子查询：   该类试卷平均得分
-- WHERE:    得分不小于 该类试卷平均得分 的用户 
-- MIN()函数：用户最低得分

SELECT
    MIN(r.score) AS min_score_over_avg
FROM exam_record r
RIGHT JOIN examination_info i 
    ON r.exam_id = i.exam_id
WHERE i.tag = 'SQL'
    AND r.score >= (
            SELECT
                AVG(r2.score)
            FROM exam_record r2
            RIGHT JOIN examination_info i2 
                ON r2.exam_id = i2.exam_id
            WHERE i2.tag = 'SQL' 
    )
;
发表于 2024-08-22 21:26:48 回复(0)
Mysql
Alina_0229头像 Alina_0229
# min(score) avg(score)
# tag=sql
# 平均分
# select
# avg(score)
# from
# exam_record as e
# join
# examination_info as f
# using(exam_id)
# where tag='SQL'

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


发表于 2024-07-14 15:51:43 回复(1)
Mysql
Tianjia99头像 Tianjia99
解题思路：
1.exam_record左关联examination_info确定统计的题目范围
2.利用窗口函数新生成一列各类试卷的平均分
3.筛选出分数不大于平均分的最小分数

select min(t1.score) as min_score_over_tag
from
(select
t.exam_id
,t.score
,t.tag
,AVG(t.score) over(partition by exam_id,tag) as avg_score_by_tag
from
(select
t1.uid
,t1.exam_id
,t1.start_time
,t1.submit_time
,t1.score
,t2.tag
from exam_record t1
left join examination_info t2 on t1.exam_id = t2.exam_id
) t ) t1
where t1.tag='SQL' and t1.score >= t1.avg_score_by_tag
;

发表于 2024-06-18 21:20:36 回复(0)
Mysql
二月清迟头像 二月清迟 
select
    min(score) min_score_over_avg
from
    (select
        score,
        avg(score) over(partition by tag) avg_score
    from
        exam_record er
    join
        examination_info ei
    on
        er.exam_id = ei.exam_id
    where
        ei.tag = "SQL" and score is not null)t1
where
    score >= avg_score;

发表于 2024-04-28 10:43:07 回复(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
勇敢的灰太狼在学习头像 勇敢的灰太狼在学习 
select min(A.score) as min_score_over_avg
from
     (select exam_record.exam_id, score
     from exam_record
     left join examination_info
     on examination_info.exam_id = exam_record.exam_id
     where tag = 'SQL' and score is not NULL) as A
where min_score_over_avg >= sum(A.score)/COUNT(A.score)
;
为什么这样显示错误呢？
编辑于 2024-04-12 20:15:25 回复(0)
Mysql
健谈的代码渣渣在敲键盘头像 健谈的代码渣渣在敲键盘
-- 先连接表，再筛选数据
with t1 as (
    select a.exam_id, a.score
    from exam_record a
    inner join examination_info b using(exam_id)
    where tag = "SQL"
)
select score as min_score_over_avg
from t1
where score >= (
    select avg(score)
    from t1
)
order by score
limit 1;

编辑于 2024-03-15 12:23:36 回复(0)
Mysql
liarzn头像 liarzn 
select 
score as min_score_over_avg
from exam_record er
inner join examination_info ei
on er.exam_id = ei.exam_id
where tag = 'SQL' and score >= (
    select avg(score)
    from from exam_record e_r
    inner join examination_info e_i
    on e_r.exam_id = e_i.exam_id
    where tag = 'SQL'
)
order by score
limit 1;这个代码有什么问题？？
发表于 2024-03-08 19:35:13 回复(0)
Mysql
69岁扶墙写python头像 69岁扶墙写python 
with tmp as (
select 
    t1.exam_id
    ,t1.score
from 
    exam_record t1
left join 
    examination_info t2 
on t1.exam_id = t2.exam_id
where t2.tag = 'SQL'
and t1.score is not null)

select 
    min(score) as min_score_over_avg
from 
    exam_record
where score >= (select avg(score) from tmp)
and exam_id in (select exam_id from examination_info where tag = 'SQL');

编辑于 2024-03-01 19:20:58 回复(0)
Mysql
muchenfeng头像 muchenfeng
select min(score) as min_score_over_avg
from (
     select  score
     from   exam_record join examination_info using(exam_id)
     where   tag="SQL" and score >=(
        select avg(score)
        from exam_record join examination_info using(exam_id)
        where  tag="SQL"
     )
)t
编辑于 2024-02-22 21:53:14 回复(0)
提交观点

问题信息

难度：
261条回答 2889浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号