题解 | #考试分数(三)#

-- 2.去除不符合排名的数据后排序
SELECT
	id,
	name,
	score
FROM
	(
	-- 1，已存在并行排名的方式查出来
	SELECT
		g.id,
		l.name,
		g.score,
		DENSE_RANK() OVER (
	PARTITION BY l.name
	ORDER BY
		g.score DESC) t
	FROM
		`language` l
	INNER JOIN grade g ON
		l.id = g.language_id
	) tmp
WHERE
	tmp.t<3
ORDER BY
	name ASC,
	score DESC