如何将四个UNION查询合并为更简洁的SQL查询且结果一致？

原查询存在多次扫描表、列名逻辑混淆、GROUP BY位置错误的问题，以下是浓缩且规范的优化方案，能得到与原查询一致的结果：

SELECT
  SUM(Earned) AS Total,
  CASE
    WHEN QuizTitle LIKE 'THEORY%' THEN 'Theory'
    WHEN QuizTitle LIKE 'HAZMAT%' THEN 'Hazmat'
    WHEN QuizTitle LIKE 'PASS%' THEN 'Pass'
    WHEN QuizTitle LIKE 'SCHOOL%' THEN 'Bus'
  END AS Category
FROM students
WHERE
  DriverLicense = 'D120001102'
  AND (
    QuizTitle LIKE 'THEORY%'
    OR QuizTitle LIKE 'HAZMAT%'
    OR QuizTitle LIKE 'PASS%'
    OR QuizTitle LIKE 'SCHOOL%'
  )
GROUP BY Category;

优化说明： #

• 单次表扫描：替代原查询多次UNION的四次表扫描，大幅提升查询效率
• 明确类别标识：通过CASE语句将QuizTitle的前缀映射为对应类别名称，避免原查询中列名混淆的问题（原UNION后列名仅保留第一个子查询的Theory，后续子查询的列名实际无效）
• 标准语法合规：按映射后的类别分组求和，符合SQL标准语法，规避原查询中GROUP BY位置错误导致的潜在语法报错

如果需要将所有类别总和展示在同一行（更适合统计报表场景），可以使用条件聚合实现：

SELECT
  DriverLicense,
  SUM(CASE WHEN QuizTitle LIKE 'THEORY%' THEN Earned ELSE 0 END) AS Theory,
  SUM(CASE WHEN QuizTitle LIKE 'HAZMAT%' THEN Earned ELSE 0 END) AS Hazmat,
  SUM(CASE WHEN QuizTitle LIKE 'PASS%' THEN Earned ELSE 0 END) AS Pass,
  SUM(CASE WHEN QuizTitle LIKE 'SCHOOL%' THEN Earned ELSE 0 END) AS Bus
FROM students
WHERE DriverLicense = 'D120001102'
GROUP BY DriverLicense;

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