SQL 在 WHERE 子句中使用别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/356675/
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 an Alias in a WHERE clause
提问by JPLemme
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):
我有一个查询，旨在向我显示表 A 中最近没有足够更新的任何行。（每一行应在“month_no”之后的 2 个月内更新。）：
SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B
WHERE A.identifier = B.identifier
AND MONTH_NO > UPD_DATE
The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
WHERE 子句中的最后一行导致“ORA-00904 无效标识符”错误。不用说，我不想在 WHERE 子句中重复整个 DECODE 函数。有什么想法吗？（修复和解决方法都被接受......）
回答by Tomalak
This is not possible directly, because chronologically, WHERE happens beforeSELECT, which always is the last step in the execution chain.
这不是直接可能的，因为按时间顺序，WHERE 发生在SELECT之前，它始终是执行链中的最后一步。
You can do a sub-select and filter on it:
您可以对其进行子选择和过滤：
SELECT * FROM
(
SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B
WHERE A.identifier = B.identifier
) AS inner_table
WHERE
MONTH_NO > UPD_DATE
Interesting bit of info moved up from the comments:
有趣的信息从评论中上移：
There should be no performance hit. Oracle does not need to materialize inner queries before applying outer conditions -- Oracle will consider transforming this query internally and push the predicate down into the inner query and will do so if it is cost effective. – Justin Cave
应该没有性能损失。在应用外部条件之前，Oracle 不需要具体化内部查询——Oracle 将考虑在内部转换此查询并将谓词下推到内部查询中，如果成本有效，将这样做。–贾斯汀洞穴
回答by me_an
SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A, table_b B
WHERE .identifier = B.identifier
HAVING MONTH_NO > UPD_DATE
回答by James
Or you can have your alias in a HAVINGclause
或者您可以在HAVING子句中使用别名
回答by J-Alex
Just as an alternative approach to you can do:
就像你可以做的另一种方法一样：
WITH inner_table AS
(SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B
WHERE A.identifier = B.identifier)
SELECT * FROM inner_table
WHERE MONTH_NO > UPD_DATE
Also you can create a permanent view for your queue and select from view.
您也可以为您的队列创建一个永久视图并从视图中选择。
CREATE OR REPLACE VIEW_1 AS (SELECT ...);
SELECT * FROM VIEW_1;
回答by Peter Aylett
It's possible to effectively define a variable that can be used in both the SELECT, WHERE and other clauses.
可以有效地定义一个可以在 SELECT、WHERE 和其他子句中使用的变量。
A subquery doesn't necessarily allow for appropriate binding to the referenced table columns, however OUTER APPLY does.
子查询不一定允许适当绑定到引用的表列，但 OUTER APPLY 允许。
SELECT A.identifier
, A.name
, vars.MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B ON A.identifier = B.identifier
OUTER APPLY (
SELECT
-- variables
MONTH_NO = TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL))
) vars
WHERE vars.MONTH_NO > UPD_DATE
Kudos to Syed Mehroz Alam.
