如何获取平均分以上的指定数量去重分数记录（含同分并排除最高分）

需要从样本表中获取平均分以上的2个不同分数对应的所有记录（包含同分），且排除该范围内的最高分，平均分也可作为筛选参考，用于获取其上下指定分数段的记录。

样本数据表 #

计算平均分 #

avg(scores) = 7.78

预期结果 #

尝试过的SQL（未得到预期结果） #

select Examinee_number, score
from examinees
where score > 
    (select avg(score)
    from examinees
    order by score
    limit 2);

select Examinee_number, score
from examinees
where score >
    (select avg(score)
    from examinees)
    order by score desc
    limit 2;

解决方案 #

方法1：子查询筛选目标分数后关联原表 #

SELECT e.id, e.scores
FROM examinees e
INNER JOIN (
    -- 筛选平均分以上的分数，排除最高分，取前2个不同分数
    SELECT DISTINCT scores
    FROM examinees
    WHERE scores > (SELECT AVG(scores) FROM examinees)
      AND scores != (SELECT MAX(scores) FROM examinees WHERE scores > (SELECT AVG(scores) FROM examinees))
    ORDER BY scores DESC
    LIMIT 2
) AS target_scores ON e.scores = target_scores.scores
ORDER BY e.scores DESC;

方法2：窗口函数排名筛选 #

SELECT id, scores
FROM (
    SELECT 
        id, 
        scores,
        -- 对平均分以上的分数按降序去重排名
        DENSE_RANK() OVER(ORDER BY scores DESC) AS score_rank
    FROM examinees
    WHERE scores > (SELECT AVG(scores) FROM examinees)
) AS ranked_data
-- 跳过最高分（rank=1），取排名2、3对应的2个不同分数的所有记录
WHERE score_rank BETWEEN 2 AND 3
ORDER BY scores DESC;

说明 #

• 方法1先通过子查询锁定符合要求的分数值，再关联原表提取所有对应记录；
• 方法2利用DENSE_RANK()窗口函数对分数进行去重排名，直接筛选出排除最高分后的前2个不同分数的记录，逻辑更简洁。

内容的提问来源于stack exchange，提问作者adi