我如何在SQL Server中引用LAG()函数列?

Question

我有一个查询，我使用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  

我得到这个错误:

Msg 207, Level 16, State 1, Line 3
无效的列名'Prev_BlncAfter'

我该如何解决这个问题?由于

Answer 1

可以在CTE中创建LAG列，而不是在外部查询中创建。例如

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;

Answer 2

您可以嵌套该查询以从外部作用域引用新添加的列，或者像之前那样创建另一个，以便在之后引用它:

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
;

Answer 3

根据评论，我将两个答案结合起来得到我需要的内容:

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;
;