SELECT g.id,language.name,g.score
FROM grade AS g INNER JOIN language
ON g.language_id=language.id
WHERE g.score>=(
SELECT DISTINCT a.score
FROM grade AS a
WHERE g.language_id=a.language_id
ORDER BY a.score DESC
LIMIT 1,1
) OR g.score=(
SELECT MAX(b.score)
FROM grade AS b
WHERE g.language_id=b.language_id
)
ORDER BY language.name ASC,g.score DESC,g.id ASC

首先找到各岗位排名为第2的分数，再找到排名第一测分数