题解 | #SQL类别高难度试卷得分的截断平均值#

1.方法一是我自己想的，最开始的方法有点繁琐；方法二和方法三是借鉴了别人的方法搞定的；烦请参阅。

/*  方法一
select a1.tag, 
       a1.difficulty,
       convert(sum(a1.score)*1.0/count(a1.uid),decimal(18,1)) clip_avg_score 
  from 
(select tag,
       difficulty,
       uid,
       score 
  from (
select tag,
       difficulty,
       uid,
       score ,
       row_number() over(partition by tag order by score) rn 
  from examination_info EI
  join exam_record ER on tag = 'SQL' and difficulty = 'hard' and EI.exam_id = ER.exam_id 
   and submit_time is not null and score is not null 
) a where a.rn > 1 
) a1 
inner join 
(select tag,
        difficulty,
        uid,
        score 
   from (
   select tag,
          difficulty,
          uid,
          score ,
          row_number() over(partition by tag order by score desc) rn 
     from examination_info EI 
     join exam_record ER on tag = 'SQL' and difficulty = 'hard' and EI.exam_id=ER.exam_id 
      and submit_time is not null and score is not null 
   ) b where b.rn>1 
) b1 
 on a1.tag = b1.tag and a1.difficulty = b1.difficulty and a1.uid=b1.uid and a1.score = b1.score 
 group by a1.tag,a1.difficulty; 
**/

/* 方法二 
select tag,
       difficulty,
       round(avg(score),1) clip_avg_score 
  from  (
    select EI.tag,EI.difficulty,ER.score ,
           row_number() over(partition by EI.tag order by score) rank1,
           row_number() over(partition by EI.tag order by score desc) rank2 
      from examination_info EI join exam_record ER on EI.exam_id = ER.exam_id 
       and EI.tag = 'SQL' 
       and EI.difficulty = 'hard' 
       and ER.score is not null 
  ) a 
  where a.rank1 > 1 and a.rank2 > 1
  group by tag,difficulty ; 
  **/

-- 方法三  
select tag,
       difficulty ,
       round((sum(score)-max(score)-min(score))/(count(score)-2),1) clip_avg_score 
  from examination_info EI join exam_record ER on EI.exam_id = ER.exam_id  
   and EI.tag = 'SQL' 
   and EI.difficulty = 'hard' 
   and ER.score is not null 
  group by tag,difficulty ;