日期:2014-05-17 浏览次数:20430 次
----------------------------
-- 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