sql - 在where子句中使用 'case expression column'

Question

SELECT ename
  ,    job
  ,    CASE deptno
         WHEN 10
           THEN 'ACCOUNTS'
         WHEN 20
           THEN 'SALES'
         ELSE 'UNKNOWN'
       END AS department
FROM emp /* !!! */ 
WHERE department = 'SALES'

这将失败:

ORA-00904: "%s: invalid identifier"

有没有一种方法可以克服Oracle 10.2 SQL中的此限制？
如何在where子句中使用“case expression column”？

Answer 1

发生此错误的原因是，按照以下顺序对SQL SELECT语句进行了逻辑 * 处理:

FROM :选择一个表或多个JOINed表以及与ON条件匹配的所有行组合。

WHERE :评估条件并删除不匹配的行。

GROUP BY :将行分组(每组折叠成一行)

HAVING :评估条件并删除不匹配的行。

SELECT :评估列列表。

DISTINCT :删除重复的行(如果它是SELECT DISTINCT语句)

UNION ， EXCEPT ， INTERSECT :该操作数的作用是在子SELECT语句的行上进行的。例如，如果它是一个UNION，则在评估所有子SELECT语句之后，将收集所有行(除非是UNION ALL，否则将消除所有重复行)。因此，对于EXCEPT或INTERSECT情况。

ORDER BY :行是有序的。

因此，您不能在 WHERE子句中使用，该子句尚未填充或计算。另请参见以下问题: oracle-sql-clause-evaluation-order

* 在逻辑上是处理的:请注意，数据库引擎还可以为查询选择其他评估顺序(这通常是它们的工作!)唯一的限制是 结果应该与上面使用的顺序相同 。

解决方案是 将查询括在另一个中:

SELECT *
FROM
  ( SELECT ename
         , job
         , CASE deptno
             WHEN 10 THEN 'ACCOUNTS'
             WHEN 20 THEN 'SALES'
                     ELSE 'UNKNOWN'
           END AS department
    FROM emp
  ) tmp
WHERE department = 'SALES' ;

或 在WHERE条件中重复计算:

SELECT ename
     , job
     , CASE deptno
         WHEN 10 THEN 'ACCOUNTS'
         WHEN 20 THEN 'SALES'
                 ELSE 'UNKNOWN'
       END AS department
FROM emp
WHERE
    CASE deptno
      WHEN 10 THEN 'ACCOUNTS'
      WHEN 20 THEN 'SALES'
              ELSE 'UNKNOWN'
    END = 'SALES' ;

我想这是查询的简化版本，或者您可以使用:

SELECT ename
     , job
     , 'SALES' AS department
FROM emp
WHERE deptno = 20 ;