Sql Server - 使用计算字段连接子查询

Question

我正在尝试计算几天之间的价格变化百分比。由于日期不是连续的，因此我在查询中构建了一个计算字段，该字段告诉我相对日期是哪一天(第 1 天、第 2 天等)。为了比较今天和昨天，我在子查询中将计算出的天数偏移 1。我想要做的是在计算出的相对日连接内部和外部查询。我想出的代码是:

SELECT TOP 11 
       P.Date,
       (AVG(P.SettlementPri) - PriceY) / PriceY as PriceChange, 
       P.Symbol,
       (RANK() OVER (ORDER BY P.Date desc)) as dayrank_Today
FROM OTE P
  JOIN (SELECT TOP 11 
               C.Date, 
               AVG(SettlementPri) as PriceY, 
               (RANK() OVER (ORDER BY C.Date desc))+1 as dayrank_Yest
          FROM OTE C
         WHERE C.ComCode = 'C-' 
      GROUP BY c.Date) C ON dayrank_Today = C.dayrank_Yest
WHERE P.ComCode = 'C-' 
GROUP BY P.Symbol, P.Date 

如果我尝试执行查询，我会收到一条错误消息，指示 dayrank_Today 是无效列。我尝试过重命名它，限定它，对它大喊脏话，然后我就蹲下了。仍然有错误。

Answer 1

您无法选择计算列，然后在联接中使用它。您可以使用我不太熟悉的 CTE，或者您可以像这样进行表选择:

SELECT 
     P.Date,       
     (AVG(AvgPrice) - C.PriceY) / C.PriceY as PriceChange, 
     P.Symbol,       
     P.dayrank_Today FROM
(SELECT TOP 11 
       ComCode,
       Date,
       AVG(SettlementPri) as AvgPrice,
       Symbol,
       (RANK() OVER (ORDER BY Date desc)) as dayrank_Today
FROM OTE WHERE ComCode = 'C-') P 
  JOIN (SELECT TOP 11 
               C.Date, 
               AVG(SettlementPri) as PriceY, 
               (RANK() OVER (ORDER BY C.Date desc))+1 as dayrank_Yest
          FROM OTE C
         WHERE C.ComCode = 'C-' 
      GROUP BY c.Date) C ON dayrank_Today = C.dayrank_Yest 
GROUP BY P.Symbol, P.Date