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

How do i use alias in where clause?

提问by Neo

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

可能重复：
在 WHERE 子句中引用列别名

   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   

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

我想在我的 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

How can i use it in where clause?

我如何在 where 子句中使用它？

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

我尝试在 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)

回答by juergen d

The SQL-Server docssays:

在SQL-服务器文档说：

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

column_alias 可用于 ORDER BY 子句，但不能用于 WHERE、GROUP BY 或 HAVING 子句。

Similar in the MySQL docit says:

类似在MySQL 文档中它说：

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.

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

In MySQLyou can at least reuse aliases in the SELECTclause

在MySQL 中，您至少可以在SELECT子句中重用别名

回答by MatBailie

You can't, not directly.

你不能，不能直接。

If you wrap the whole query in a sub-query, however, it works fine.

但是，如果您将整个查询包装在一个子查询中，它就可以正常工作。

SELECT
  *
FROM
(
  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
)
  AS data
WHERE
  Position = 1   

This means that you don't need to repeat the CASEstatement in WHEREclause. (Maintainable and DRY).

这意味着您不需要重复CASEinWHERE子句中的语句。（可维护和干燥）。

It is also a structure that allows the optimiser to behave as ifyou hadsimply repeated yourself in the WHEREclause.

这也是一个结构，使优化器的行为，就好像你已经简单地重复自己的WHERE条款。

It's also very portable to other RDBMSs.

它对于其他 RDBMS 也非常便携。

In SQL Server, then you also have another option...

在 SQL Server 中，您还有另一种选择...

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,
  position.val AS Position
from
  Fireball_Reporting..Trade
CROSS APPLY
(
  SELECT
    case
      when (Buy = 1 and Long = 1) then 1
      when (Buy = 0 and Long = 0) then 1
                                  else 0
    end AS val
)
  AS position
where
  porfolioid = 5
  AND position.val = 1

回答by Stuart1044

You can't directly do this...but you can wrap an additional select around it all and use the where clause:

您不能直接执行此操作……但是您可以将附加的 select 全部包裹起来并使用 where 子句：

    select * from 
   (   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 
    )x
    where x.position = 1

回答by whytheq

I'm probably missing something but surely this will cover it:

我可能遗漏了一些东西，但这肯定会涵盖它：

WHERE (Buy = 1 and Long = 1) OR (Buy = 0 and Long = 0)

WHERE (Buy = 1 and Long = 1) OR (Buy = 0 and Long = 0)