求SQL语句（在线等）

[Justin_chen]

想了很久，都不知道怎么开始写，求各位老大帮助。

我现在有一个表，如表一，
里面储存的是每年物料每周的需求数量，从第1周至52周
第0周，储存的是目前仓库的库存量。

我现在想得到表二，

w0(现有量）- 最早一次需求（201311 13年11周 )，如果大于0，这这周就等于0（不需再购，有库存）
然后继续下一周
W0-W11-W12(500-300-300),这时-100，表明这一周需要购100，因此这里显示100。
之后就不需要再减，直接取表一中的需求数量。

如果现W0等于负数，那需要把这个需求和算在第一个需求上。
（如W0=-19,W11 100 ，那么W11=19+100 =119，之后的周就直接取表一的需求）

表一：
WEEK ItemID Qty
201300 1001 500
201311 1001 300
201312 1001 300
201313 1001 500
201314 1001 0
……
201352 1001

201300 1002 0
201311 1002 300
201312 1002 90
……


表二：
WEEK ItemID Qty
201311 1001 0 (500-300)>0
201312 1001 100 (500-300-300)=abs(-100)
201313 1001 500 (如果上一个结果小于0，从这行开始就等于表一值)
201314 1001 0
……
201352 1001

201300 1002 0
201311 1002 300
201312 1002 90
……

[chuifeng2012]

哥们 咱俩的问题好像有点类似啊 http://bbs.csdn.net/topics/390431456

[Justin_chen]

解决问题了，非常感谢OrchidCat ，又学到东西了。结贴

[Mr_Nice]

if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1] (WEEK int,ItemID int,Qty int)
insert into [TB1]
select 201300,1001,500 union all
select 201311,1001,300 union all
select 201312,1001,300 union all
select 201313,1001,500 union all
select 201314,1001,0 union all
select 201300,1002,0 union all
select 201311,1002,300 union all
select 201312,1002,90

select * from [TB1]

WITH TT
AS
(SELECT WEEK,itemid,CASE WHEN RIGHT(CONVERT(VARCHAR,week),2) = '00' THEN qty ELSE -qty END AS qty FROM tb1),

T1
AS(
SELECT TOP 100 A.week,a.itemid,a.qty,CASE WHEN SUM(b.qty) >0 THEN 0 ELSE SUM(b.qty) END AS sqty,CASE WHEN SUM(b.qty)<0 THEN 1 ELSE 0 END AS mark
FROM TT A
INNER JOIN TT B ON A.itemid = B.itemid AND A.[week]>=b.[week]
GROUP BY A.week,a.itemid,a.qty
ORDER BY a.itemid,a.week),

T2
AS(
SELECT *,ROW_NUMBER() OVER(PARTITION BY itemid,mark ORDER BY week ) AS num
FROM  T1)


SELECT week,itemid,CASE WHEN sqty <0 AND num =1 THEN ABS(sqty) ELSE ABS(qty) END AS qty
FROM T2


/*
week	itemid	qty
201300	1001	500
201311	1001	300
201312	1001	100
201313	1001	500
201314	1001	0
201300	1002	0
201311	1002	300
201312	1002	90*/

[Justin_chen]

谢谢楼上的，我马上测试一下。非常感谢。

[Justin_chen]

您好，OrchidCat。我将您的语句移到生产环境后， 测试了一下，还有一些小问题，比如下列: WEEK ITEMID QTY 00 10-03-091MT27 94 13 10-03-091MT27 -66 14 10-03-091MT27 -116 16 10-03-091MT27 -58 17 10-03-091MT27 -214 19 10-03-091MT27 -188 20 10-03-091MT27 -80 21 10-03-091MT27 -105 查询结果为： week itemid qty 00 10-03-091MT27 94 13 10-03-091MT27 66 这里应该是0 14 10-03-091MT27 88 这里应该是 60 16 10-03-091MT27 58 17 10-03-091MT27 214 19 10-03-091MT27 188 20 10-03-091MT27 80 21 10-03-091MT27 105 这应该是哪里没注意到，请指出。谢谢。

[Mr_Nice]

引用 4 楼 steedch 的回复:

您好，OrchidCat。我将您的语句移到生产环境后， 测试了一下，还有一些小问题，比如下列: WEEK ITEMID QTY 00 10-03-091MT27 94 13 10-03-091MT27 -66 14 10-03-091MT27 -116 16 10-03-091MT27 -58 17 10-03-091MT27 -2……

;WITH TT
AS
(SELECT WEEK,itemid,CASE WHEN RIGHT(CONVERT(VARCHAR,week),2) = '00' THEN qty ELSE - qty END AS qty 
FROM tb1),
 
T1
AS(
SELECT TOP 100 A.week,a.itemid,a.qty,CASE WHEN SUM(b.qty) > 0 THEN 0 ELSE SUM(b.qty) END AS sqty,
CASE WHEN SUM(b.qty)<0 THEN 1 ELSE 0 END AS mark
FROM TT A
INNER JOIN TT B ON A.itemid = B.itemid AND A.[week] >= b.[week]
GROUP BY A.week,a.itemid,a.qty
ORDER BY a.itemid,a.week
),
 
T2
AS(
SELECT *,ROW_NUMBER() OVER(PARTITION BY itemid,mark ORDER BY week ) AS num
FROM  T1)

SELECT  week ,
        itemid ,
        CASE WHEN EXISTS ( SELECT   1    --这个地方加个判断即可
                           FROM     T2
                           WHERE    sqty < 0 )
             THEN CASE WHEN sqty < 0
                            AND num = 1 THEN ABS(sqty)
                       ELSE ABS(qty)
                  END
             ELSE CASE WHEN num = 1 THEN ABS(qty)
                       ELSE 0
                  END
        END AS qty
FROM    T2