Oracle SQL查询如何添加筛选平均分重复学生的条件

你可以通过窗口函数或子查询匹配两种方式实现平均分重复记录的筛选，两种方案逻辑均和你原有业务逻辑完全兼容： #

方案1：窗口函数实现（推荐，Oracle 11g及以上版本支持，性能更优） #

SELECT studentname, average
FROM (
    SELECT 
        s.studentname,
        AVG(cs.exam_season_one + cs.exam_season_two + cs.degree_season_one + cs.degree_season_two)/4 AS average,
        -- 统计相同平均分对应的学生数量
        COUNT(*) OVER(PARTITION BY AVG(cs.exam_season_one + cs.exam_season_two + cs.degree_season_one + cs.degree_season_two)/4) AS avg_count
    FROM courses_student cs
    JOIN students s ON s.student_id = cs.student_id
    JOIN SECTION se ON s.sectionid = se.sectionid
    WHERE cs.courses_id = 1
      AND se.classes_id IN (2,5) -- 等价于原有OR写法，可读性更强
      AND s.studentname LIKE 'm%'
    GROUP BY s.studentname
) t
WHERE avg_count > 1;

逻辑说明：内层查询保留你原有统计逻辑，新增的窗口函数会按计算出的平均分分组计数，同一平均分的所有学生都会拿到相同的计数值，外层过滤掉计数值为1的记录即可得到所有平均分重复的学生。 #

方案2：子查询匹配实现（兼容所有Oracle版本） #

WITH student_avg AS (
    -- 先计算所有符合条件学生的平均分
    SELECT 
        s.studentname,
        AVG(cs.exam_season_one + cs.exam_season_two + cs.degree_season_one + cs.degree_season_two)/4 AS average
    FROM courses_student cs
    JOIN students s ON s.student_id = cs.student_id
    JOIN SECTION se ON s.sectionid = se.sectionid
    WHERE cs.courses_id = 1
      AND se.classes_id IN (2,5)
      AND s.studentname LIKE 'm%'
    GROUP BY s.studentname
)
SELECT a.*
FROM student_avg a
-- 关联出现次数大于1的平均分集合
JOIN (
    SELECT average
    FROM student_avg
    GROUP BY average
    HAVING COUNT(*) > 1
) repeat_avg ON a.average = repeat_avg.average;

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