Oracle SQL中CASE语句的THEN子句能否返回多个值？求解决方案

原查询的问题点 #

• CASE表达式用法错误：Oracle的CASE是返回单个值的表达式，不能在THEN子句中直接写a.DETAIL_CODE IN ('A','B','C')这类条件判断，这会导致逻辑不成立甚至语法报错。
• 子查询逻辑冗余且混乱：子查询完全没必要，还错误引用了外层的t.DETAIL_CODE，逻辑方向完全偏离需求——你需要的是根据输入的TERM_CODE匹配对应DETAIL集合，而非自关联表查询。

替代方案 #

方案一：直接用条件判断匹配 #

外层已经过滤了TERM_CODE = :term，直接通过OR组合对应条件即可，逻辑清晰高效：

SELECT t.ID,
       t.TERM_CODE,
       t.DETAIL_CODE
FROM tableName t
WHERE t.TERM_CODE = :term
  AND (
    (:term = '202310' AND t.DETAIL_CODE IN ('A','B','C'))
    OR (:term = '202320' AND t.DETAIL_CODE IN ('D','E','F'))
    OR (:term = '202330' AND t.DETAIL_CODE IN ('G','H','I'))
  );

也可以嵌套CASE表达式实现相同逻辑：

SELECT t.ID,
       t.TERM_CODE,
       t.DETAIL_CODE
FROM tableName t
WHERE t.TERM_CODE = :term
  AND CASE :term
        WHEN '202310' THEN CASE WHEN t.DETAIL_CODE IN ('A','B','C') THEN 1 ELSE 0 END
        WHEN '202320' THEN CASE WHEN t.DETAIL_CODE IN ('D','E','F') THEN 1 ELSE 0 END
        WHEN '202330' THEN CASE WHEN t.DETAIL_CODE IN ('G','H','I') THEN 1 ELSE 0 END
        ELSE 0
      END = 1;

方案二：用虚拟映射表关联（扩展性更强） #

如果后续需要新增更多TERM_CODE与DETAIL_CODE的映射关系，用WITH子句构建虚拟映射表再关联，维护更方便：

WITH term_detail_map AS (
  SELECT '202310' AS TERM_CODE, 'A' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202310' AS TERM_CODE, 'B' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202310' AS TERM_CODE, 'C' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202320' AS TERM_CODE, 'D' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202320' AS TERM_CODE, 'E' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202320' AS TERM_CODE, 'F' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202330' AS TERM_CODE, 'G' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202330' AS TERM_CODE, 'H' AS DETAIL_CODE FROM DUAL UNION ALL
  SELECT '202330' AS TERM_CODE, 'I' AS DETAIL_CODE FROM DUAL
)
SELECT t.ID,
       t.TERM_CODE,
       t.DETAIL_CODE
FROM tableName t
JOIN term_detail_map m
  ON t.TERM_CODE = m.TERM_CODE
  AND t.DETAIL_CODE = m.DETAIL_CODE
WHERE t.TERM_CODE = :term;

后续新增映射只需在term_detail_map中添加UNION ALL行即可，无需修改核心查询逻辑。

内容的提问来源于stack exchange，提问作者Vivek Simha Pathi