题解 125| #得分不小于平均分的最低分#

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

【分类】：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