SQL在Where语句中使用别名。

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

我使用该软件2005

10 个解决方案

#1

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

不需要任何子查询或操作。

#2

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”。在适当的条件下使用时要小心，以免出现性能问题。

#3

Use a subquery:

使用子查询:

SELECT * 
FROM 
  (SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1 FROM MyTable)
WHERE Col1 = 'MySearch'

#4

You can do this:

你可以这样做:

SELECT Col1
FROM ( SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1 ) AS x
WHERE Col1 = 'MySearch'

#5

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中工作)。

#6

I think it is not possible, but maybe you can take a look on Common Table Expressions over SQL 2005

我认为这是不可能的，但是也许您可以看看SQL 2005中的通用表表达式

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'

#7

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'

#8

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优化器没有您想象的那么笨，所以我将再次重复子字符串表达式。

#9

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'

#10

With PostgreSQL 9.3+ OR Oracle 12c, there is now lateral join that allows creating an alias.

使用PostgreSQL 9.3+或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.

使用这种语法，您不必使用'*'，这可能会导致所有列都无法执行或重新复制。

#1