题解 | SQL273 考试分数(三)

-- sql273 考试分数（三）
WITH t1 AS (
	SELECT 
    	id,
    	language_id,
    	score,
    	DENSE_RANK() OVER(PARTITION BY language_id ORDER BY score DESC) AS ranking 
    FROM grade
),
t2 AS (
	SELECT
    	id,
    	language_id,
    	score
    FROM t1
    WHERE ranking <= 2
),
t3 AS (
	SELECT
    	t2.id,
    	l.name,
    	t2.score
    FROM t2
    LEFT JOIN language l
    ON t2.language_id = l.id
    ORDER BY l.name ASC,t2.score DESC,t2.language_id ASC
)
SELECT * FROM t3;

# 记得进行三重排序功能实现！