SQL 在 where 子句中使用“case 表达式列”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6545664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using 'case expression column' in where clause
提问by EugeneP
SELECT ename
, job
, CASE deptno
WHEN 10
THEN 'ACCOUNTS'
WHEN 20
THEN 'SALES'
ELSE 'UNKNOWN'
END AS department
FROM emp /* !!! */
WHERE department = 'SALES'
This fails:
这失败了：
ORA-00904: "%s: invalid identifier"
ORA-00904: "%s: 无效标识符"
Is there a way to overcome this limitation in Oracle 10.2 SQL ? How to use the 'case expression column' in where clause ?
有没有办法克服 Oracle 10.2 SQL 中的这个限制？如何在 where 子句中使用“case 表达式列”？
回答by ypercube??
The reason for this error is that SQL SELECTstatements are logically*processed in the following order:
出现这个错误的原因是SQLSELECT语句按照以下顺序进行逻辑*处理：
FROM: selection of one table or many JOINed ones and all rows combinations that match theONconditions.WHERE: conditions are evaluated and rows that do not match are removed.GROUP BY: rows are grouped (and every group collapses to one row)HAVING: conditions are evaluated and rows that do not match are removed.SELECT: list of columns is evaluated.DISTINCT: duplicate rows are removed (if it's a SELECT DISTINCT statement)UNION,EXCEPT,INTERSECT: the action of that operand is taken upon the rows of sub-SELECT statements. For example, if it's a UNION, all rows are gathered (and duplicates eliminated unless it's a UNION ALL) after all sub-SELECT statements are evaluated. Accordingly for the EXCEPT or INTERSECT cases.ORDER BY: rows are ordered.
FROM: 选择一个表或多个 JOINed 的表以及符合ON条件的所有行组合。WHERE: 评估条件并删除不匹配的行。GROUP BY：行被分组（每组折叠为一行）HAVING: 评估条件并删除不匹配的行。SELECT：评估列列表。DISTINCT: 删除重复的行（如果是 SELECT DISTINCT 语句）UNION,EXCEPT,INTERSECT: 该操作数的操作是在子 SELECT 语句的行上执行的。例如，如果它是 UNION，则在评估所有 sub-SELECT 语句后收集所有行（并消除重复，除非它是 UNION ALL）。因此，对于 EXCEPT 或 INTERSECT 情况。ORDER BY: 行是有序的。
Therefore, you can't use in WHEREclause, something that hasn't been populated or calculated yet. See also this question: oracle-sql-clause-evaluation-order
因此，您不能使用 inWHERE子句，这是尚未填充或计算的内容。另见这个问题：oracle-sql-clause-evaluation-order
* logicallyprocessed:Note that database engines may as well choose another order of evaluation for a query (and that's what they usually do!) The only restriction is that the results should be the same as if the above order was used.
*逻辑处理：请注意，数据库引擎也可以为查询选择其他评估顺序（这就是他们通常所做的！）唯一的限制是结果应该与使用上述顺序相同。
Solution is to enclose the query in another one:
解决方案是将查询包含在另一个查询中：
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' ;
or to duplicate the calculation in the WHERE condition:
或复制 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' ;
I guess this is a simplified version of your query or you could use:
我想这是您查询的简化版本，或者您可以使用：
SELECT ename
, job
, 'SALES' AS department
FROM emp
WHERE deptno = 20 ;
回答by Martin Schapendonk
Your table does not contain a column "department" and thus you can not reference it in your where clause. Use deptno instead.
您的表不包含“部门”列，因此您不能在 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;
回答by Cyril Gandon
This work for me :
这对我有用：
SELECT ename, job
FROM emp
WHERE CASE WHEN deptno = 10 THEN 'ACCOUNTS'
WHEN deptno = 20 THEN 'SALES'
ELSE 'UNKNOWN'
END
= 'SALES'
回答by Amitābha
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';
回答by Himansh Gautam
Oracle tries to filter the number of records to be scanned from table by going for the where clause first before select that is why your query fails. Moreover, your query would have never returned rows with department - "Accounts or Unknown" because of the filter Department="SALES"
Oracle 尝试通过在 select 之前先查找 where 子句来过滤要从表中扫描的记录数，这就是查询失败的原因。此外，由于筛选器 Department="SALES"，您的查询永远不会返回带有部门的行 - “Accounts or Unknown”
Try below instead, that will be easy to be fetched by Engine :
试试下面，这将很容易被 Engine 获取：
SELECT ename, job,'SALES' AS department FROM emp WHERE deptno = 20;
从 emp WHERE deptno = 20 中选择 ename、job、'SALES' 作为部门
回答by marchaos
try:
尝试：
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';
