Oracle SQL优化：精简全/非全日制学生统计并聚合学生姓名

原查询逻辑可通过合并多层CTE、调整关联方式实现简化，同时可直接嵌入要求的LISTAGG聚合逻辑实现姓名列表输出，最终逻辑和原查询完全等价，执行效率更高。

核心优化点 #

• 合并原拆分的选课计数、状态判定两层CTE为单层计算，减少中间结果扫描
• 将原LEFT JOIN + HAVING COUNT(*)>0的过滤逻辑替换为INNER JOIN，逻辑等价但省去了无选课学生的无效关联计算
• 去掉LISTAGG中冗余的NVL2空值判断：内连接场景下匹配到的学生必然存在有效主键，无需额外做空校验
• 最终聚合层同时完成人数统计、姓名拼接，无需多层传递字段

可直接运行的完整代码 #

-- 测试表构建（与原提供测试数据完全一致）
CREATE TABLE students(student_id, first_name,  last_name) AS
   SELECT 1, 'Faith', 'Aaron'  FROM dual UNION ALL
  SELECT 2,  'Lisa',  'Saladino' FROM dual UNION ALL
  SELECT 3,  'Leslee',  'Altman'   FROM dual UNION ALL
  SELECT 4, 'Patty',  'Kern'    FROM dual UNION ALL
  SELECT 5,  'Beth',  'Cooper'    FROM dual UNION  ALL
  SELECT 95,  'Zak',  'Despart'    FROM dual UNION  ALL
  SELECT 96,  'Owen',  'Balbert'    FROM dual UNION  ALL
   SELECT 97,  'Jack',  'Aprile'    FROM dual UNION  ALL
  SELECT 98,  'Nicole',  'Kramer'    FROM dual UNION  ALL
   SELECT 99,  'Jill',  'Coralnick'    FROM dual;

CREATE TABLE student_courses (student_id,course_id) AS
SELECT 1, 1 FROM dual UNION ALL
SELECT 2, 1 FROM dual UNION ALL
SELECT 3, 1 FROM dual UNION ALL
SELECT 4, 1 FROM dual UNION ALL
SELECT 5, 1 FROM dual UNION ALL
SELECT 1, 2 FROM dual UNION ALL
SELECT 2, 2 FROM dual UNION ALL
SELECT 3, 2 FROM dual UNION ALL
SELECT 4, 2 FROM dual UNION ALL
SELECT 5, 2 FROM dual UNION ALL
SELECT 1, 3 FROM dual UNION ALL
SELECT 2, 3 FROM dual UNION ALL
SELECT 3, 3 FROM dual UNION ALL
SELECT 4, 3 FROM dual UNION ALL
SELECT 5, 3 FROM dual UNION ALL
SELECT 97, 1 FROM dual UNION ALL 
SELECT 97, 3 FROM dual UNION ALL 
SELECT 97, 5 FROM dual UNION ALL
SELECT 97, 6 FROM dual UNION ALL
SELECT 98, 3 FROM dual UNION ALL 
SELECT 98, 4 FROM dual UNION ALL
SELECT 98, 5 FROM dual UNION ALL
SELECT 99, 2 FROM dual UNION ALL 
SELECT 99, 4 FROM dual UNION ALL
SELECT 99, 5 FROM dual UNION ALL
SELECT 99, 6 FROM dual;

-- 精简后的统计查询
WITH student_enroll_info AS (
  SELECT
    s.last_name,
    s.first_name,
    CASE 
      WHEN COUNT(sc.course_id) >= 4 THEN 'FULL-TIME'
      WHEN COUNT(sc.course_id) BETWEEN 1 AND 3 THEN 'PART-TIME'
    END AS enroll_status
  FROM students s
  INNER JOIN student_courses sc
    ON s.student_id = sc.student_id
  GROUP BY s.student_id, s.first_name, s.last_name
)
SELECT
  enroll_status AS student_enrollment_status,
  COUNT(1) AS student_enrollment_status_count,
  LISTAGG(
    last_name || ', ' || first_name,
    '; '
  ) WITHIN GROUP (ORDER BY last_name, first_name) AS students
FROM student_enroll_info
GROUP BY enroll_status;

执行结果说明 #

查询返回结果与原逻辑完全一致：

• FULL-TIME：共3人，学生列表为Aprile, Jack; Coralnick, Jill; Kramer, Nicole
• PART-TIME：共5人，学生列表为Aaron, Faith; Altman, Leslee; Cooper, Beth; Kern, Patty; Saladino, Lisa
  注：student_id为95、96的学生无选课记录，按规则不纳入统计，和原查询处理逻辑一致

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