使用“case 表达式列”；在 where 子句中

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: 无效标识符”

有没有办法克服 Oracle 10.2 SQL 中的此限制？ 如何在 where 子句中使用“case 表达式列”？

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 ;

Answer 2

您的表不包含“部门”列，因此您无法在 where 子句中引用它。使用 deptno 代替。

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

Answer 3

这对我有用：

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

Answer 4

select emp_.*
from (SELECT ename
  ,    job
  ,    CASE deptno
         WHEN 10
           THEN 'ACCOUNTS'
         WHEN 20
           THEN 'SALES'
         ELSE 'UNKNOWN'
       END AS department
FROM emp /* !!! */ ) emp_ where emp_.department='UNKNOWN';

Answer 5

尝试：

  SQL> SELECT ename
      2  ,      job
      3  ,      CASE
      4            WHEN  deptno = 10
      5            THEN 'ACCOUNTS'
      6            WHEN  deptno = 20
      7            THEN 'SALES'
     12            ELSE 'UNKNOWN'
     13         END AS department
     14  FROM   emp /* !!! */ where department = 'SALES';

Answer 6

Oracle 尝试通过在 select 之前先使用 where 子句来过滤要从表中扫描的记录数，这就是查询失败的原因。此外，您的查询永远不会返回包含部门 - “Accounts or Unknown”的行，因为过滤器 Department=“SALES”

请尝试下面的操作，这将很容易被引擎获取：

SELECT ename, job,'SALES' AS Department
来自员工
其中部门编号 = 20；