我正在SQL Server 2017 Windows 10中编写一段代码。我不知道问题出在哪里,也无法得到大小写表达式的结果。
这是我的代码
select PD.PONO, PM.CURRSTATUS, PD.Qty
, case when pm.currstatus = 1 then sum(pd.qty) end TotalStock
from ppom PM
LEFT OUTER join ppod PD on pd.pono = pm.pono
where pm.pono = 15973
group by PD.PONO, PM.CURRSTATUS, PD.Qty
GO
下方显示结果
PONO CURRSTATUS Qty TotalStock
15973 1 8 8
15973 1 14 14
15973 1 25 25
而结果应该显示的和值
PONO CURRSTATUS Qty TotalStock
15973 1 8 47
15973 1 14 47
15973 1 25 47
请帮我一下,谢谢
您似乎想要一个窗口求和而不是聚合。它看起来像:
select
pd.pono,
pm.currstatus,
pd.qty,
sum(case when pm.currstatus = 1 then pd.qty else 0 end)
over(partition by pd.pono) total_stock
from ppom pm
left join ppod pd on pd.pono = pm.pono