SQL需求：计算 Tag 字段为SQL，difficulty字段为hard的试卷的截断平均值

  • 1条件截断平均值： 去掉一个最大值，一个最小值后在求平均

    思路

  • 1 将难度表 过滤tag,和difficulty 和 成绩表 过滤成绩为空的合并 

    select  tag,exam_id,difficulty   from examination_infotest  where tag = "SQL" and difficulty="hard") as ei
left join 
exam_record as er
on ei.exam_id = er.exam_id 
where score is not null

  • 2 过滤掉最大值和最小值难点 union 不能直接和 order by 结合 通过外围套表解决

select id from 
(select id,score from exam_record  as er
right join  (select exam_id  from  examination_infotest where tag = "SQL" and difficulty="hard")as ei
on ei.exam_id = er.exam_id
where score is not null order by score desc limit 1 ) as max_tab

union all

select id from 
(select id,score from exam_record as er  
right join  (select exam_id  from  examination_infotest  where tag = "SQL" and difficulty="hard")as ei
on ei.exam_id = er.exam_id
where score  is not null order by score asc limit 1) as min_tab

3 通过拼接代码 返回目标结果(完整代码如下)

select tag,difficulty,round(clip_avg_score,1) as clip_avg_score  from (
select er.exam_id,min(tag) as tag,min(difficulty) as difficulty ,avg(score) as clip_avg_score  from 
(select  tag,exam_id,difficulty   from examination_info  where tag = "SQL" and difficulty="hard") as ei

left join 

exam_record as er
on ei.exam_id = er.exam_id 
where score is not null 

and id not in 

(select id from 
(select id,score from exam_record  as er
right join  (select exam_id  from  examination_info where tag = "SQL" and difficulty="hard")as ei
on ei.exam_id = er.exam_id
where score is not null order by score desc limit 1 ) as max_tab
union all
select id from 
(select id,score from exam_record as er  
right join  (select exam_id  from  examination_info  where tag = "SQL" and difficulty="hard")as ei
on ei.exam_id = er.exam_id
where score  is not null order by score asc limit 1) as min_tab)
group by exam_id) as tmp