Py学习  »  DATABASE

为什么mysql中不允许在group by中使用别名?[复制品]

Backrub32 • 4 年前 • 393 次点击  

可能重复:
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)
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/43923
 
393 次点击  
文章 [ 4 ]  |  最新文章 4 年前
whytheq
Reply   •   1 楼
whytheq    11 年前

我可能漏掉了一些东西,但这肯定能弥补:

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

Stuart1044
Reply   •   2 楼
Stuart1044    11 年前

你不能直接这样做,但是你可以在它周围包上一个附加的选择,并使用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
MatBailie
Reply   •   3 楼
MatBailie    11 年前

你不能,不能直接。

但是,如果将整个查询包装在子查询中,则它可以正常工作。

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   

这意味着你不需要重复 CASE 语句在 WHERE 条款。(可维护且干燥)。

它也是一种结构,允许乐观者表现 犹如 只是重复你自己 哪里 条款。

它对其他RDBMS也是非常便携的。


在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
juergen d
Reply   •   4 楼
juergen d    11 年前

标准SQL 禁止在where子句中引用列别名 . 施加此限制是因为在计算where子句时,可能尚未确定列值。

Taken from MySQL Doc

CyrnNoLayas可以用在ORDY by子句中,但是 不能在where、group by或having子句中使用 .

Taken from the MSSQL Doc