如何在where子句中使用别名? [英] How do i use alias in where clause?

问题描述

可能重复:
引用WHERE子句中的列别名

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，这是大小写的别名

i want to use Position =1 in my where clause which is an alias of case

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

如何在where子句中使用它?

How can i use it in where clause?

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

I tried directly use that CASE statement in where clause but failed please help me

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)

推荐答案

SQL-Server文档说:

column_alias可以在ORDER BY子句中使用，但不能在WHERE，GROUP BY或HAVING子句中使用.

与 MySQL文档相似它说:

标准SQL 不允许在WHERE子句中引用列别名.之所以施加此限制，是因为在评估WHERE子句时，可能尚未确定列值.

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 中，您可以在 SELECT 子句中至少重用别名

In MySQL you can at least reuse aliases in the SELECT clause

这篇关于如何在where子句中使用别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！