sql - 如何在 where 子句中使用别名？

Question

这个问题已经有答案了:

已关闭 10 年前。

Possible Duplicate:
Referring to a Column Alias in a WHERE Clause

   SELECT
Trade.TradeId, 
Isnull(Securities.SecurityType,'Other') SecurityType, 
TableName,
CASE 
WHEN 
SecurityTrade.SecurityId IS NOT NULL  
THEN 
SecurityTrade.SecurityId
ELSE 
Trade.SecurityId
END AS PricingSecurityID,
sum(Trade.Quantity)OVER(Partition by Securities.SecurityType, SecurityTrade.SecurityId,Trade.Price, Buy,Long ) as sumQuantity,
--added porfolio id for Getsumofqantity
Trade.PortfolioId,

Trade.Price,
case
when (Buy = 1 and Long = 1) then 1
when (Buy = 0 and Long = 0) then 1
else 0
end Position
from
Fireball_Reporting..Trade

where porfolioid =5 and Position =1   

我想在我的 where 子句中使用 Position =1，它是 case 的别名

case
when (Buy = 1 and Long = 1) then 1
when (Buy = 0 and Long = 0) then 1
else 0
end Position

如何在 where 子句中使用它？

我尝试在 where 子句中直接使用该 CASE 语句，但失败了。

WHERE Trade.SecurityId = @SecurityId AND PortfolioId = @GHPortfolioID AND
                (case when (Buy = 1 and Long = 1) then 1 when (Buy = 0 and Long = 0) then 1 else 0 end Position = 1)

Answer 1

SQL-Server docs说:

column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.

类似于MySQL doc它说:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

在MySQL中，您至少可以reuse aliases in the SELECT clause