如何在SQLServer中參考LAG()函式列？-有解無憂

我有一個使用LAG函式的查詢：

WITH Tr AS 
(
    SELECT 
        DocDtls.Warehouse, Transactions.Code, DocDtls.zDate, 
        Transactions.ID, Transactions.QtyIn, Transactions.QtyOut, 
        Transactions.BalanceAfter
    FROM 
        DocDtls 
    INNER JOIN 
        Transactions ON DocDtls.[PrimDocNum] = Transactions.[DocNum]
)
SELECT
    ID, Code, QtyIn, QtyOut, BalanceAfter, 
    LAG(BalanceAfter, 1, 0) OVER (PARTITION BY Warehouse, Code 
                                  ORDER BY Code, ID) Prev_BlncAfter 
FROM 
    Tr;

它作業正常，但是當我嘗試在之前添加此列時FROM：

SUM(Prev_BlncAfter   QtyIn) - QtyOut AS NewBlncAfter

我收到此錯誤：

訊息 207，級別 16，狀態 1，第 3 行
無效的列名稱“Prev_BlncAfter”

我怎樣才能解決這個問題？謝謝

uj5u.com熱心網友回復：

您可以LAG在 CTE 內部而不是在外部查詢中創建列。例如

declare @DocDtls table (Warehouse int, zDate date, [PrimDocNum] int);
declare @Transactions table (code int, id int, QtyIn int, QtyOut int, balanceafter int, [DocNum] int)
;with Tr As 
(
    SELECT 
        d.Warehouse
        , t.Code
        , d.zDate
        , t.ID
        , t.QtyIn
        , t.QtyOut
        , t.BalanceAfter
        ,LAG(BalanceAfter,1,0) Over (partition by Warehouse,Code order by Code,ID) Prev_BlncAfter
    FROM @DocDtls d
    INNER JOIN @Transactions t ON d.[PrimDocNum] = t.[DocNum]
)
select ID,Code,QtyIn,QtyOut,BalanceAfter
,SUM(Prev_BlncAfter   QtyIn)-QtyOut As NewBlncAfter
 from Tr 
 group by ID,Code,QtyIn,QtyOut,BalanceAfter;

uj5u.com熱心網友回復：

您可以嵌套此查詢以從外部范圍參考新添加的列，或者像之前所做的那樣創建另一個以在之后參考它：

with Tr As (
  SELECT
    DocDtls.Warehouse,
    Transactions.Code,
    DocDtls.zDate,
    Transactions.ID,
    Transactions.QtyIn,
    Transactions.QtyOut,
    Transactions.BalanceAfter
  FROM
    DocDtls
    INNER JOIN Transactions ON DocDtls.[PrimDocNum] = Transactions.[DocNum]
),
formatted_tr as (
  select
    ID,
    Code,
    QtyIn,
    QtyOut,
    BalanceAfter,
    LAG(BalanceAfter, 1, 0) Over (
      partition by Warehouse,
      Code
      order by
        Code,
        ID
    ) Prev_BlncAfter
  from
    Tr
  )
select
  SUM(Prev_BlncAfter   QtyIn) - QtyOut As NewBlncAfter
from
  formatted_tr
group by 
  ID, QtyOut
;

uj5u.com熱心網友回復：

根據評論，我結合了兩個答案來獲得我需要的東西：

with Tr As (
  SELECT
    DocDtls.Warehouse,
    Transactions.Code,
    DocDtls.zDate,
    Transactions.ID,
    Transactions.QtyIn,
    Transactions.QtyOut,
    Transactions.BalanceAfter
  FROM
    DocDtls
    INNER JOIN Transactions ON DocDtls.[PrimDocNum] = Transactions.[DocNum]
),
formatted_tr as (
  select
    ID,
    Code,
    QtyIn,
    QtyOut,
    BalanceAfter,
    LAG(BalanceAfter, 1, 0) Over (
      partition by Warehouse,
      Code
      order by
        Code,zDate,ID
    ) Prev_BlncAfter
  from
    Tr
  )
select ID,Code,QtyIn,QtyOut,BalanceAfter
,SUM(Prev_BlncAfter   QtyIn)-QtyOut As NewBlncAfter
 from formatted_tr
 group by ID,Code,QtyIn,QtyOut,BalanceAfter;
;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/479623.html

標籤：sql服务器

上一篇：根據sql選擇查詢C#的結果更新表列的最佳方法是什么？

下一篇：.NET-獲取兩個日期之間的資料