oracle 在 where 子句中使用 select 子句中的列号。提取别名原始名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9866141/
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 column number from select clause in where clause. Extracting allias original name
提问by Vjerci
So lets say i have a query like this
所以让我们说我有一个这样的查询
SELECT a as d,b,c FROM myTable
WHERE a=1;
Is it possible instead of a=1 to type something like SELECTED.1 = 1 or to somehow extract allias original name since d=1 doesn't work
是否可以代替 a=1 键入类似 SELECTED.1 = 1 的内容或以某种方式提取别名的原始名称，因为 d=1 不起作用
回答by Ben Lee
It's not possible to do this because of internal complexities about when the WHERE clause gets evaluated. But if the thing you are aliasing is a long expression that you'd rather not repeat, there is a typical solution to this. From https://forums.oracle.com/forums/thread.jspa?threadID=1107532:
由于有关何时评估 WHERE 子句的内部复杂性，因此无法执行此操作。但是，如果您要混淆的内容是一个您不想重复的长表达式，那么有一个典型的解决方案。来自https://forums.oracle.com/forums/thread.jspa?threadID=1107532：
The standard solution to this is, you move the query into an inline view (without the where-clause predicate), and then add the where-clause predicate, using the alias, in the outer query.
So something like this:
select ... from (select ... here complex expression that is aliased ... from ... where ) A where ... here condition that uses the A.alias column ...
对此的标准解决方案是，将查询移动到内联视图中（没有 where-clause 谓词），然后在外部查询中使用别名添加 where-clause 谓词。
所以像这样：
select ... from (select ... here complex expression that is aliased ... from ... where ) A where ... here condition that uses the A.alias column ...
In your example case, that would be:
在您的示例中，这将是：
SELECT d, b, c
FROM ( SELECT a AS d, b, c FROM myTable ) AS myAliasedTable
WHERE d = 1
But of course, this wouldn't make sense in your literal example. If the thing you are aliasing is just a column name, then just use the actual column name in the WHERE, no real drawback in that case.
但是，当然，这在您的字面示例中没有意义。如果您的别名只是一个列名，那么只需在 WHERE 中使用实际的列名，在这种情况下没有真正的缺点。
Also note that if you do use this method, you should put as much of the WHERE clause as you can in the internal query (meaning the parts that don't reference an aliased column) to limit the size of the resulting aliased table. For example, if you also wanted to test on bin your example, that would be:
另请注意，如果您确实使用此方法，则应在内部查询中尽可能多地放置 WHERE 子句（意味着不引用别名列的部分）以限制生成的别名表的大小。例如，如果您还想b在您的示例中进行测试，那就是：
SELECT d, b, c
FROM (
SELECT a AS d, b, c
FROM myTable
WHERE b = 1
) AS myAliasedTable
WHERE d = 1
回答by onedaywhen
Use a common table expression (CTE)e.g.
使用公用表表达式 (CTE)例如
WITH T
AS
(
SELECT a as d, b, c
FROM myTable
)
SELECT *
FROM T
WHERE d = 1;
