SQL的CTE（公共表表达式）优化子查询

• SQL的CTE（公共表表达式）是一种通过WITH关键字定义的临时结果集，相对于用子查询，可以增强查询的可读性和复用性。
• 案例:
  • 创建一张成绩表

    CREATE TABLE academic_records (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
        student_id VARCHAR(10) NOT NULL COMMENT '学生唯一标识符',
        student_name VARCHAR(50) NOT NULL COMMENT '学生姓名',
        subject VARCHAR(20) NOT NULL COMMENT '学科名称',
        score DECIMAL(5,1) NOT NULL COMMENT '数值成绩（0-100分，允许一位小数）',
        CHECK (score BETWEEN 0 AND 100)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生学业成绩记录表';
    

  • 填充数据

    -- 关闭自动提交，保证事务一致性
    SET autocommit = 0;
    
    -- 插入核心数据
    INSERT INTO academic_records (student_id, student_name, subject, score)
    SELECT 
      students.student_id,
      students.student_name,
      subjects.subject,
      ROUND(RAND() * 90 + 10, 1) AS score -- 生成10-100之间的随机成绩
    FROM 
      (-- 生成10个学生基础信息
       SELECT 'S2023001' AS student_id, '张一鸣' AS student_name UNION ALL
       SELECT 'S2023002', '李思思' UNION ALL
       SELECT 'S2023003', '王伟' UNION ALL
       SELECT 'S2023004', '赵敏' UNION ALL
       SELECT 'S2023005', '陈强' UNION ALL
       SELECT 'S2023006', '周晓晓' UNION ALL
       SELECT 'S2023007', '黄轩' UNION ALL
       SELECT 'S2023008', '吴倩' UNION ALL
       SELECT 'S2023009', '郑浩' UNION ALL
       SELECT 'S2023010', '林小美'
      ) students
    CROSS JOIN 
      (-- 定义三门学科
       SELECT '语文' AS subject UNION ALL
       SELECT '数学' UNION ALL
       SELECT '英文'
      ) subjects;
    
    -- 提交事务
    COMMIT;
    

  • 查询学生的成绩以及和班级平均分的差值
    • 子查询

      SELECT 
          main.student_id AS 学号,
          main.student_name AS 姓名,
          main.subject AS 学科,
          main.score AS 成绩,
          CONCAT(
              IF(ROUND(main.score - sub.avg_score, 2) >= 0, '+', ''),
              ROUND(main.score - sub.avg_score, 2)
          ) AS 平均分差值
      FROM academic_records main
      INNER JOIN (
          -- 学科平均分子查询
          SELECT 
              subject,
              ROUND(AVG(score), 2) AS avg_score
          FROM academic_records
          GROUP BY subject
      ) sub ON main.subject = sub.subject
      ORDER BY 
          main.student_id,
          FIELD(main.subject, '语文', '数学', '英文');
      

    • CTE表达式

      WITH avg_score AS(
      -- 学科平均分子查询
          SELECT 
              subject,
              ROUND(AVG(score), 2) AS avg_score
          FROM academic_records
          GROUP BY subject
      )	
      SELECT 
          main.student_id AS 学号,
          main.student_name AS 姓名,
          main.subject AS 学科,
          main.score AS 成绩,
          CONCAT(
              IF(ROUND(main.score - avg_score.avg_score, 2) >= 0, '+', ''),
              ROUND(main.score - avg_score.avg_score, 2)
          ) AS 平均分差值
      FROM academic_records main
      INNER JOIN avg_score ON main.subject = avg_score.subject
      ORDER BY 
          main.student_id,
          FIELD(main.subject, '语文', '数学', '英文');
      

      • 相当于将子查询抽取到外面了，后续再使用avg_score查询结果的时候可以多处引用。且这样的结构更加清晰。
      • 注意mysql8以上支持