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

/*解法一
SELECT ei.tag, ei.difficulty, round(avg(er.score), 1) clip_avg_score
FROM (
	SELECT *, RANK() OVER (ORDER BY score DESC) de_rk,
			  RANK() OVER (ORDER BY score) as_rk
	FROM exam_record
	WHERE score is not null
	) AS er
INNER JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE er.as_rk != 1 AND er.de_rk != 1 AND ei.tag = 'SQL' and ei.difficulty = 'hard'
GROUP BY ei.tag, ei.difficulty
 */
/*解法二：新建一个表t*/
WITH t AS (
        SELECT ei.tag, ei.difficulty, er.score, RANK() OVER (ORDER BY er.score DESC) de_rk,
                                       RANK() OVER (ORDER BY er.score) as_rk
        FROM exam_record er
        INNER JOIN examination_info ei 
		USING (exam_id)
        WHERE
            score is not null
            and ei.tag = 'SQL'
            and ei.difficulty = 'hard'
    )
SELECT  tag, difficulty, round(avg(score),1) as clip_avg_score
FROM t
WHERE t.de_rk != 1 and t.as_rk != 1
GROUP BY tag, difficulty

饿解法一：将成绩记录表er和考试信息表ei连接起来，在连接之前先在er里面增加两列排序的，窗口函数，这样在计算阶段平均分时可以将降序和升序为1的都去掉。同时，要将score为空的也去掉，因为在用RANK()函数时，NULL值也会进行排序。GROUP BY 是为了能够在SELECT中显示字段

解法二：将两个表连接之后，增加排序的两个列，作为一个新表t，再从表t中检索想要的信息