Oracle SQL UNION排序问题及HackerRank The PADS题报错求助

错误原因分析 #

1. 第一个报错（ORA-00933）：UNION 操作符不允许在单个子查询后单独使用 ORDER BY。UNION 是合并多个结果集的操作，只能在整个合并语句的末尾添加一次 ORDER BY 来统一排序。
2. 第二个报错（ORA-00904）：合并后的结果集中不存在 NAME 字段——第二个子查询（统计职业次数）并未返回 NAME，因此直接用 ORDER BY NAME 会触发无效标识符错误。

正确SQL代码 #

SELECT result
FROM (
    -- 生成姓名+职业首字母的结果行
    SELECT NAME || '(' || SUBSTR(OCCUPATION, 1, 1) || ')' AS result,
           1 AS priority,
           NAME AS sort1,
           NULL AS sort2,
           NULL AS sort3
    FROM OCCUPATIONS
    UNION ALL
    -- 生成职业统计结果行
    SELECT 'There are a total of ' || COUNT(*) || ' ' || LOWER(OCCUPATION) || 's.' AS result,
           2 AS priority,
           NULL AS sort1,
           COUNT(*) AS sort2,
           LOWER(OCCUPATION) AS sort3
    FROM OCCUPATIONS
    GROUP BY OCCUPATION
)
ORDER BY priority, sort1, sort2, sort3;

代码说明 #

• UNION ALL：替代 UNION，因为两个子查询的结果不会重复，避免不必要的去重计算，提升查询效率。
• 优先级列（priority）：确保姓名结果行（priority=1）先输出，统计结果行（priority=2）后输出，符合题目要求的两个结果集顺序。
• 排序辅助列：
  • 姓名行按 sort1（即 NAME）字母顺序排序，满足第一部分的要求。
  • 统计行按 sort2（职业次数）升序排序，次数相同时按 sort3（小写职业名）字母顺序排序，满足第二部分的要求。

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