if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(日期 date,ID int,WL01_01_GUID Nvarchar(50),单价 numeric(28,2),数量 numeric(28,2),累计数量 numeric(28,2),加权平均单价 numeric(28,2))
Insert into #T Select ****
Go
with cte as (
SELECT * FROM #T WHERE id = 1
UNION ALL
select a.日期,a.ID,a.WL01_01_GUID,a.单价,a.数量,
CAST((a.数量 + b.累计数量) as numeric(28,2)) as 累计数量
,
(case
when isnull(a.单价,0) = 0 then cast(b.加权平均单价 AS numeric(28,2))
else cast((b.加权平均单价*b.累计数量+ a.单价*a.数量)/(b.累计数量+a.数量) as numeric(28,2)) end
) as 加权平均单价
FROM #T a
JOIN cte b ON a.ID = b.ID + 1)