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

SELECT
	a.tag,
	a.difficulty,
	b.clip_avg_score
FROM
	examination_info a
JOIN (
	SELECT
		exam_id,
		FORMAT(
			(
				SUM(score) - MAX(score) - MIN(score)
			) / (COUNT(*) - 2),
			1
		) AS clip_avg_score
	FROM
		exam_record
	WHERE
		exam_id = (
			SELECT
				exam_id
			FROM
				examination_info
			WHERE
				tag = 'SQL'
			AND difficulty = 'hard'
		)
	AND score IS NOT NULL
) b ON a.exam_id = b.exam_id