22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @结存 int
SET @结存=25
;WITH /* 测试数据
table1(月份,入库,出库)AS(
SELECT 2,0,0 UNION ALL
SELECT 3,30,30 UNION ALL
SELECT 4,10,5 UNION ALL
SELECT 5,20,5 UNION ALL
SELECT 6,40,10 UNION ALL
SELECT 7,10,50 UNION ALL
SELECT 8,20,5
),*/
a AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY 月份 DESC) -1 库龄
FROM table1
)
,b AS (
SELECT a.*,
@结存-(a.入库-a.出库) AS 结存
FROM a
WHERE 库龄 = 0
UNION ALL
SELECT a.*,
b.结存-(a.入库-a.出库) AS 结存
FROM a
JOIN b
ON a.库龄 = b.库龄+1
WHERE b.结存 <> 0
)
SELECT *
FROM b
ORDER BY 库龄 DESC
月份 入库 出库 库龄 结存
----------- ----------- ----------- -------------------- -----------
4 10 5 4 0
5 20 5 3 5
6 40 10 2 20
7 10 50 1 50
8 20 5 0 10