SQL 在 Where 语句中使用别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/715462/
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
SQL Use alias in Where statement
提问by Melursus
I wounder how I could use an alias in a where statement.
我想知道如何在 where 语句中使用别名。
Example :
例子 ：
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM MyTable
WHERE Col1 = 'MySearch'
I use MSSQL 2005
我使用 MSSQL 2005
采纳答案by David Snabel-Caunt
Not possible, but you can do the following:
不可能，但您可以执行以下操作：
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM MyTable
WHERE SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) = 'MySearch'
No subqueries or hacks required
不需要子查询或黑客
回答by arnolem
You can use "having" instead of "where".
您可以使用“拥有”而不是“地点”。
SELECT
SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM
MyTable
HAVING
Col1 = 'MySearch'
Having do a "where" after execution of the query. Be careful to use it in the right conditions to have no performance problem.
执行查询后执行“where”。请小心在正确的条件下使用它，以免出现性能问题。
回答by cgp
Use a subquery:
使用子查询：
SELECT *
FROM
(SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1 FROM MyTable)
WHERE Col1 = 'MySearch'
回答by Frederik Gheysels
You can do this:
你可以这样做：
SELECT Col1
FROM ( SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1 ) AS x
WHERE Col1 = 'MySearch'
回答by Dave Costa
SELECT * FROM (
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM MyTable
)
WHERE Col1 = 'MySearch'
(I know this works in Oracle, I believe it is standard SQL and would work in MSSQL.)
（我知道这在 Oracle 中有效，我相信它是标准 SQL 并且可以在 MSSQL 中使用。）
回答by Jhonny D. Cano -Leftware-
I think it is not possible, but maybe you can take a look on Common Table Expressions over SQL 2005
我认为这是不可能的，但也许您可以查看 SQL 2005 上的 Common Table Expressions
Like this:
像这样：
WITH MyCTE( Col1) AS
(
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM MyTable
)
SELECT *
FROM MyCTE
WHERE Col1 = 'MySearch'
回答by zvolkov
Actually, using alias won't make your query any faster as SQL optimizer is not as dumb as you think, so I'd just repeat the SUBSTRING expression again.
实际上，使用别名不会使您的查询更快，因为 SQL 优化器并不像您想象的那么愚蠢，所以我只是再次重复 SUBSTRING 表达式。
回答by JMM
use a view or a derived table.
使用视图或派生表。
Using a derived table, your example would look like:
使用派生表，您的示例如下所示：
select col1
from
(SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM MyTable)
where col1='Mysearch'
回答by bormat
With PostgreSQL 9.3+ OR Oracle 12c, there is now lateral join that allows creating an alias.
使用 PostgreSQL 9.3+ OR Oracle 12c，现在有横向连接允许创建别名。
Lateral joins are joints inside witch you can reference preceding tables.
横向连接是女巫内部的连接，您可以参考前面的表格。
SELECT col1, col2,col3
FROM MyTable m
JOIN LATERAL (
SELECT SUBSTRING(m.Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
) x ON true
WHERE Col1 = 'MySearch'
With this syntax, you don't have to use '*' that can be non-performing or recopy all the columns.
使用此语法，您不必使用可能导致性能不佳或重新复制所有列的“*”。
回答by Russ Cam
The answer is you can't - you can do this
答案是你不能 - 你可以这样做
SELECT
SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM
MyTable
WHERE
SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) = 'MySearch'
