如何获得有限数量的高于平均水平的不同记录?

Question

如何使2个不同的记录(包括具有相同分数的记录)高于平均值?不包括最顶端的

这是我的示例表

<表类> id 分数 tbody><<tr>1118.501207.451239.131277.702226.002327.773216.803426.904536.665649.056668.508768.90

在MySQL 8+上，这里我们可以使用RANK()解析函数。为了找出除去前两名的平均分数，我们可以尝试:

WITH cte AS (
SELECT score, RANK() OVER (ORDER BY score DESC) rnk
FROM examinees
)
SELECT AVG(score)
FROM cte
WHERE rnk > 2;

select * from `score` where score > (WITH new AS (select * from `score` where score > (SELECT ROUND(AVG(score), 2) FROM `score`) ORDER BY score limit 2) select AVG(score) from new);
select * from score where score < (SELECT AVG(score) FROM `score`) ORDER BY score;