如何在MS SQL中继续从union select中运行总计计算?

Question

我有两个使用Union All连接的查询。第一个计算期初余额，第二个返回行和列。我需要将第一个查询的计算余额结转到第二个查询。

这是我的表:

ItemTrans
id    ItemNo    InQty    OutQty   DateAdded   
100   'A001'    0        100      '2020-12-20'
101   'A002'    600      0        '2020-12-20'
102   'A001'    500      0        '2020-12-21'
103   'A002'    0        110      '2020-12-20'
104   'A002'    0        75       '2020-12-20'
105   'A001'    0        50       '2020-12-22'
106   'A001'    10       0        '2021-01-05'
107   'A001'    0        20       '2021-01-06'
108   'A001'    0        10       '2021-01-07'
109   'A001'    0        10       '2021-01-08'

这是我的查询:

Select 
'Opening bal' as 'IDNo',
'' as ItemNo,
'2020-12-31' as 'Date Posted/ As of',
0 as QTY,
Balance = (sum(InQty) - sum(OutQty)) 
From itemTrans
Where ItemNo = 'A001'
AND DateAdded < '2021-01-01'
Union All
Select 
cast(id as nvarchar(20)) as 'IDNo',
ItemNo,
DateAdded as 'Date Posted/ As of',
QTY = InQty - OutQty,
Case
When InQty > 0 Then sum(InQty) OVER (ORDER BY id)
When OutQty > 0 Then sum(OutQty*-1) OVER (ORDER BY id)
else 0
End as Balance
From ItemTrans
Where ItemNo = 'A001'
AND DateAdded >= '2021-01-01'

我的查询需要返回以下内容:

IDNo           ItemNo    Date Posted/ As of    QTY    Balance
Opening bal              2020-12-31            0      350
106            A001      2021-01-05            10     360
107            A001      2021-01-06            -20    340
108            A001      2021-01-07            -10    330
109            A001      2021-01-08            -10    210

但是我的查询返回:

IDNo           ItemNo    Date Posted/ As of    QTY    Balance
Opening bal              2020-12-31            0      350
106            A001      2021-01-05            10     10
107            A001      2021-01-06            -20    -20
108            A001      2021-01-07            -10    -30
109            A001      2021-01-08            -10    -40

似乎运行的InQty与OutQty是分开计算的

Answer 1

你是正确的。查询的每个部分都是独立的，并且只在该部分上计算运行和。您需要对它进行子查询，并确保它的顺序正确:

SELECT
IDNo,
ItemNo,
[Date Posted/ As of],
QTY,
Case
When InQty > 0 Then sum(InQty) OVER (ORDER BY ordering, id)
When OutQty > 0 Then sum(OutQty*-1) OVER (ORDER BY ordering, id)
else 0
End AS Balance
FROM (
Select 
0 as id,
0 as ordering,
'Opening bal' as IDNo,
'' as ItemNo,
'2020-12-31' as [Date Posted/ As of],
0 as QTY,
AddBalance = (sum(InQty) - sum(OutQty)),
sum(InQty) InQty,
sum(OutQty) OutQty
From itemTrans
Where ItemNo = 'A001'
AND DateAdded < '2021-01-01'
Union All
Select 
id,
1,
cast(id as nvarchar(20)),
ItemNo,
DateAdded,
InQty - OutQty,
0,
InQty,
OutQty
From ItemTrans
Where ItemNo = 'A001'
AND DateAdded >= '2021-01-01'
) t

使用[]而不是''来用空格引用列名

Answer 2

从上一个答案中得到灵感，我想出了下面的查询，它给了我所需的结果。

SELECT
IDNo,
ItemNo,
[Date Posted/ As of],
QTY,
sum(Balance) OVER (ORDER BY id) as Balance
FROM 
(
Select 
0 as id,
'Opening bal' as IDNo,
'' as ItemNo,
'2020-12-31' as [Date Posted/ As of],
0 as QTY,
Balance = (sum(InQty) - sum(OutQty))
From itemTrans
Where ItemNo = 'A001'
AND DateAdded < '2021-01-01'
Union All
Select 
id,
cast(id as nvarchar(20)) as IDNo,
ItemNo,
DateAdded as [Date Posted/ As of],
InQty - OutQty,
InQty - OutQty as Balance
From ItemTrans
Where ItemNo = 'A001'
AND DateAdded >= '2021-01-01'
)t