各位大神，求一SQL语句
各位大神,表结构如下
表是按照Item和Mon排序,对每一个Item,SkQty唯一。
ItemMonSkQtyDemQty
11-00115-10
11-00125-8
11-001356
11-00145-3
11-002110-3
11-002210-9
11-0023105
11-0024103
要求的报表是SkQty首先加最小mon的DemQty，如果大于0，则继续加下一个Mon的DemQty，如果小于0，则在下个Mon取DemQty。如果取的DemQty<0,在下个Mon继续取DemQty；如果取的DemQty>0,则在下个Mon加上DemQty,以此类推。如果大于0，则加上下一个Mon的DemQty,小于0则取DemQty。结果如下：
ItemMonSkQtyQty解释
11-00115-55+(-10)
11-00125-8上一个Mon的Qty<0,取DemQty
11-001356上一个Mon的Qty<0,取DemQty
11-001453上一个Mon的Qty>0,则加上DemQty，6+(-3)
11-002110710+(-3)
11-002210-27+(-9)
11-0023105
11-00241085+3
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-19 12:02:25
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Item] varchar(6),[Mon] int,[SkQty] int,[DemQty] int)
insert [huang]
select '11-001',1,5,-10 union all
select '11-001',2,5,-8 union all
select '11-001',3,5,6 union all
select '11-001',4,5,-3 union all
select '11-002',1,10,-3 union all
select '11-002',2,10,-9 union all
select '11-002',3,10,5 union all
select '11-002',4,10,3
--------------开始查询--------------------------
;WITH cte AS (
select *,ROW_NUMBER() OVER(PARTITION BY item ORDER BY Item,Mon)id
from [huang]),
cte2 AS
(
SELECT item,mon,skqty,skqty+[DemQty] AS [DemQty],id
FROM cte
WHERE id=1
UNION ALL
SELECT a.item,a.mon,a.skqty,CASE WHEN b.DemQty<0 THEN a.demqty ELSE a.[DemQty]+b.[DemQty] END demqty, a.id
FROM cte a INNER JOIN cte2 b ON a.id=b.id+1 AND a.item=b.item
)
SELECT item,mon,skqty,[DemQty]
FROM cte2 ORDER BY item,mon
----------------结果----------------------------
/*
item mon skqty DemQty
------ ----------- ----------- -----------
11-001 1 5 -5
11-001 2 5 -8
11-001 3 5 6
11-001 4 5 3
11-002 1 10 7
11-002 2 10 -2
11-002 3 10 5
11-002 4 10 8
*/
