SQL 在 where 子句中使用计算字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3884678/
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
Use a calculated field in the where clause
提问by Luc M
Is there a way to use a calculated field in the whereclause?
有没有办法在where子句中使用计算字段？
I want to do something like
我想做类似的事情
SELECT a, b, a+b as TOTAL FROM (
select 7 as a, 8 as b FROM DUAL
UNION ALL
select 8 as a, 8 as b FROM DUAL
UNION ALL
select 0 as a, 0 as b FROM DUAL
)
WHERE TOTAL <> 0
;
but I get ORA-00904: "TOTAL": invalid identifier.
但我明白了ORA-00904: "TOTAL": invalid identifier。
So I have to use
所以我必须使用
SELECT a, b, a+b as TOTAL FROM (
select 7 as a, 8 as b FROM DUAL
UNION ALL
select 8 as a, 8 as b FROM DUAL
UNION ALL
select 0 as a, 0 as b FROM DUAL
)
WHERE a+b <> 0
;
回答by Shannon Severance
Logically, the selectclause is one of the last parts of a query evaluated, so the aliases and derived columns are not available. (Except to order by, which logicallyhappens last.)
从逻辑上讲，该select子句是所评估的查询的最后一部分之一，因此别名和派生列不可用。（除了order by，逻辑上最后发生。）
Using a derived table is away around this:
使用派生表是远离这个：
select *
from (SELECT a, b, a+b as TOTAL FROM (
select 7 as a, 8 as b FROM DUAL
UNION ALL
select 8 as a, 8 as b FROM DUAL
UNION ALL
select 0 as a, 0 as b FROM DUAL)
)
WHERE TOTAL <> 0
;
