题目
请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
示例数据 exam_record表(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
examination_info表(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间)
加粗的语句不正确,为什么呢?
where如果先执行的话不就应该先筛选出大于平均值的score然后在select进行min操作吗
是哪里逻辑出现问题了吗
为什么一定还要嵌套一个
select min(score) as min_score_over_avg
from exam_record join examination_info using(exam_id)
where tag='SQL' and** score>= avg(score)**
看到参考的正确答案
select min(e_r.score) as min_score_over_avg
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where e_i.tag = 'SQL'
and score >= (select avg(e1.score)
from exam_record e1 join examination_info e2
on e1.exam_id = e2.exam_id
where tag = 'SQL'
)