SQL 在查询中使用别名并使用它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2055126/
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 alias in query and using it
提问by asb
I have a doubt and question regarding alias in sql. If i want to use the alias in same query can i use it. For eg: Consider Table name xyz with column a and b
我对 sql 中的别名有疑问和疑问。如果我想在同一个查询中使用别名，我可以使用它。例如：考虑带有 a 列和 b 列的表名 xyz
select (a/b) as temp , temp/5 from xyz
Is this possible in some way ?
这在某种程度上可能吗？
采纳答案by Josh
You are talking about giving an identifier to an expression in a query and then reusing that identifier in other parts of the query?
您正在谈论为查询中的表达式提供标识符，然后在查询的其他部分重用该标识符？
That is not possible in Microsoft SQL Server which nearly all of my SQL experience is limited to. But you can however do the following.
这在 Microsoft SQL Server 中是不可能的，我几乎所有的 SQL 经验都仅限于此。但是您可以执行以下操作。
SELECT temp, temp / 5
FROM (
SELECT (a/b) AS temp
FROM xyz
) AS T1
Obviously that example isn't particularly useful, but if you were using the expression in several places it may be more useful. It can come in handy when the expressions are long and you want to group on them too because the GROUP BY clause requires you to re-state the expression.
显然，该示例并不是特别有用，但如果您在多个地方使用该表达式，它可能会更有用。当表达式很长并且您也想对它们进行分组时，它会派上用场，因为 GROUP BY 子句要求您重新声明表达式。
In MSSQL you also have the option of creating computed columns which are specified in the table schema and not in the query.
在 MSSQL 中，您还可以选择创建在表架构中而不是在查询中指定的计算列。
回答by Guru
You can use Oracle withstatement too. There are similar statements available in other DBs too. Here is the one we use for Oracle.
您也可以使用 Oraclewith语句。其他数据库中也有类似的语句。这是我们用于 Oracle 的。
with t
as (select a/b as temp
from xyz)
select temp, temp/5
from t
/
This has a performance advantage, particularly if you have a complex queries involving several nested queries, because the WITHstatementis evaluated only once and used in subsequent statements.
这具有性能优势，尤其是当您有一个涉及多个嵌套查询的复杂查询时，因为该WITH语句只计算一次并在后续语句中使用。
回答by onedaywhen
Not possible in the same SELECTclause, assuming your SQL product is compliant with entry level Standard SQL-92.
不可能在同一SELECT个子句中，假设您的 SQL 产品符合入门级标准 SQL-92。
Expressions (and their correlation names) in the SELECTclause come into existence 'all at once'; there is no left-to-right evaluation that you seem to hope for.
SELECT子句中的表达式（及其相关名称）“一次性”出现；没有您似乎希望的从左到右的评估。
As per @Josh Einstein's answer here, you can use a derived table as a workaround (hopefully using a mote meaningful name than 'temp' and providing one for the temp/5expression -- have in mind the person who will inherit your code).
根据@Josh Einstein 在这里的回答，您可以使用派生表作为解决方法（希望使用比“temp”更有意义的名称并为temp/5表达式提供一个名称- 请记住将继承您的代码的人）。
Note that code you posted would work on the MS Access Database Engine (and would assign a meaningless correlation name such as Expr1to your second expression) but then again it is not a real SQL product.
请注意，您发布的代码可以在 MS Access 数据库引擎上运行（并且会为Expr1您的第二个表达式分配一个无意义的相关名称），但它又不是真正的 SQL 产品。
回答by Chris
Its possible I guess:
我猜它可能：
SELECT (A/B) as temp, (temp/5)
FROM xyz,
(SELECT numerator_field as A, Denominator_field as B FROM xyz),
(SELECT (numerator_field/denominator_field) as temp FROM xyz);
回答by Jebil
This is now available in Amazon Redshift
现已在 Amazon Redshift 中提供
E.g.
例如
select clicks / impressions as probability, round(100 * probability, 1) as percentage from raw_data;
Ref:
参考：
回答by Filip Ekberg
You might find W3Schools "SQL Alias"to be of good help.
您可能会发现W3Schools“SQL 别名”很有帮助。
Here is an example from their tutorial:
这是他们教程中的一个示例：
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p,
Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola'
Regarding using the Alias further in the query, depending on the database you are using it might be possible.
关于在查询中进一步使用别名，根据您使用的数据库，它可能是可能的。
