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

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 表达式列”？

- EugeneP

---

这看起来是正确的，你能把你的错误粘贴过来吗？ - Giann

904. 00000 - "%s: 无效标识符" *原因： *操作：

- EugeneP

正确答案是Martin Schapendonk的答案。问题不在于你的“case”。 - Giann

Answer 1

这个错误的原因是SQL SELECT语句按照以下顺序进行逻辑*处理：

• FROM：选择一个表或连接多个表，并选择与ON条件匹配的所有行组合。

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

• GROUP BY：对行进行分组（每个组都折叠为一行）

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

• SELECT：评估列列表。

• DISTINCT：删除重复的行（如果是SELECT DISTINCT语句）

• UNION、EXCEPT、INTERSECT：该操作符的作用取决于子查询语句的行。例如，如果是UNION，则在评估所有子查询语句后，聚集所有行（并消除重复项，除非是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 ;

- ypercubeᵀᴹ

---

是的，这个可以工作，但有点过载。看起来这是完成工作的唯一正确方式。谢谢。 - EugeneP

你的答案是唯一一个解决了问题的正确答案。我无法投票支持它，因为我没有登录，但我选择它作为最佳答案。 - EugeneP

请仅返回翻译后的文本：请检查“在WHERE条件中重复计算”是指第二个查询..这是错误的... tmp表没有deptno列。 - pratik garg

鉴于解析顺序，如果您可以在WHERE或GROUP BY子句中为计算/聚合定义别名，那将非常方便。然后，您可以执行SELECT itemName, FirstLetter, Count(itemName) as n FROM table1 GROUP BY itemName, substring(itemName, 1,1) as FirstLetter（基于先前链接的SO线程中的问题的示例）。是否有任何DB引擎支持此修改后的语法？ - mpag

@mpag 我不知道有哪个DBMS有这样的功能。只有MySQL有类似的功能，但是是相反的。在SELECT中定义的别名可以在某些子句中使用，例如GROUP BY和HAVING。 - ypercubeᵀᴹ

Answer 2

您的表中并没有名为"department"的列，因此您无法在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;

- Martin Schapendonk

---

查询结果包含此列，我猜这是Oracle架构的限制。无论是虚拟字段还是数据库事物，游标都包含部门列。 - EugeneP

你可以随意称呼它，但这就是 Oracle 数据库的工作方式。 - Martin Schapendonk

@EugeneP：这不是Oracle的限制，而是SQL的限制。这是因为WHERE条件必须在SELECT列填充（或计算）之前进行评估。 - ypercubeᵀᴹ

或者（虽然没有实际意义）...WHERE CASE deptno WHEN 10 THEN 'ACCOUNTS' WHEN 20 THEN 'SALES' ELSE 'UNKNOWN' END = 'SALES' - niktrs

你可以在 department 上使用谓词和嵌套查询，例如 SELECT * FROM (SELECT ename, job, CASE ... END AS department FROM emp) WHERE department = 'SALES'。 - Dave Costa

Answer 3

这对我很有效：

这适用于我：

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

- Cyril Gandon

---

这是一个好方法。我正在搜索并发现你的解决方案完美地适用于我。谢谢朋友。 - Smile

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';

- 王奕然

---

我相信@ypercube已经涵盖了这个选项。 - Andriy M

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';

- marchaos

---

这个失败的原因和 OP 的尝试一样：你不能在 WHERE 子句中引用 SELECT 的列（在这种情况下是 department）。你只能在 ORDER BY 和 HAVING 子句中这样做。 - MatBailie

Answer 6

Oracle尝试通过先使用where子句而不是select来过滤要扫描的记录数，这就是为什么您的查询失败的原因。此外，由于过滤器Department="SALES"，您的查询永远不会返回部门为“Accounts或Unknown”的行。

请改用以下查询，这将更容易被引擎获取：

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

- Himansh Gautam