求一个应用问题的方法

Question

fitem    fdata                  fbillno   fbegqty        fqty            fendqty
 
A01 2013-01-01 00:00:00.000 Win001 100.0000000000 70.0000000000 0.0000000000
A01 2013-01-01 00:00:00.000 Win002 100.0000000000 20.0000000000 0.0000000000
A01 2013-01-03 00:00:00.000 Out001 100.0000000000 -30.0000000000 0.0000000000
A02 2013-01-02 00:00:00.000 win003 200.0000000000 80.0000000000 0.0000000000
A02 2013-01-03 00:00:00.000 win004 200.0000000000 60.0000000000 0.0000000000
A02 2013-01-04 00:00:00.000 Out002 200.0000000000 -60.0000000000 0.0000000000
A02 2013-01-04 00:00:00.000 Out003 200.0000000000 -10.0000000000 0.0000000000
A03 2013-01-04 00:00:00.000 win005 90.0000000000 8.0000000000 0.0000000000
A03 2013-01-04 00:00:00.000 win006 90.0000000000 9.0000000000 0.0000000000
A03 2013-01-04 00:00:00.000 Out004 90.0000000000 6.0000000000 0.0000000000需要按不同的fitem跟新fendqty,例如A01的第一条记录 fendqty=fbegqty+fqty=100+70=170,第二条记录fendqty=170+20=190.  A02的第一条记录fendqty=fbegqty+fqty=200+80=280,第二条记录fendqty=280+60=340,如何进行更新

Answer 1

with tb(fitem,fdata,fbillno,fbegqty,fqty,fendqty)
as(
select 'A01','2013-01-01','Win001',100,70,0 union all
select 'A01','2013-01-01','Win002',100,20,0 union all
select 'A01','2013-01-03','Out001',100,-30,0 union all
select 'A02','2013-01-02','win003',200,80,0 union all
select 'A02','2013-01-03','win004',200,60,0 union all
select 'A02','2013-01-04','Out002',200,-60,0 union all
select 'A02','2013-01-04','Out003',200,-10,0 union all
select 'A03','2013-01-04','win005',90,8,0 union all
select 'A03','2013-01-04','win006',90,9,0 union all
select 'A03','2013-01-04','Out004',90,6,0
),
tc as(
select *,row=row_number()over(partition by fitem order by fdata,fbillno) from tb
),
cte as(
select fitem,fdata,fbillno,fbegqty,fqty,fendqty=cast(fbegqty+fqty as decimal(28,2)),row from tc where row=1
union all
select t.fitem,t.fdata,t.fbillno,t.fbegqty,t.fqty,cast(c.fendqty+t.fqty as decimal(28,2)),t.row from cte c join tc t on c.row+1=t.row and c.fitem=t.fitem
)
select fitem,fdata,fbillno,fbegqty,fqty,fendqty from cte order by fitem,row

Answer 2

CREATE TABLE #TABLENAME(fitem NVARCHAR(10),fdata DATETIME,fbillno NVARCHAR(10)
,fbegqty  DECIMAL(10,2), fqty  DECIMAL(10,2),fendqty  DECIMAL(10,2))
INSERT INTO #TABLENAME 
SELECT 'A01','2013-01-01 00:00:00.000','Win001',100.0000000000,70.0000000000,0.0000000000
UNION ALL
SELECT 'A01','2013-01-01 00:00:00.000','Win002',100.0000000000,20.0000000000,0.0000000000
UNION ALL
SELECT 'A01','2013-01-03 00:00:00.000','Out001',100.0000000000,-30.0000000000,0.0000000000
UNION ALL
SELECT 'A02','2013-01-02 00:00:00.000','win003',200.0000000000,80.0000000000,0.0000000000
UNION ALL
SELECT 'A02','2013-01-03 00:00:00.000','win004',200.0000000000,60.0000000000,0.0000000000
UNION ALL
SELECT 'A02','2013-01-04 00:00:00.000','Out002',200.0000000000,-60.0000000000,0.0000000000
UNION ALL
SELECT 'A02','2013-01-04 00:00:00.000','Out003',200.0000000000,-10.0000000000,0.0000000000
UNION ALL
SELECT 'A03','2013-01-04 00:00:00.000','win005',90.0000000000,8.0000000000,0.0000000000
UNION ALL
SELECT 'A03','2013-01-04 00:00:00.000','win006',90.0000000000,9.0000000000,0.0000000000
UNION ALL
SELECT 'A03','2013-01-04 00:00:00.000','Out004',90.0000000000,6.0000000000,0.0000000000SELECT ROW_NUMBER() OVER(partition by fitem ORDER BY fdata) ID,* 
INTO #TEMP01 
FROM #TABLENAME SELECT *,(SELECT SUM(CASE WHEN ID=1 THEN fbegqty ELSE 0 END+fqty) FROM #TEMP01 WHERE A.fitem=fitem AND ID<=A.ID ) 
FROM #TEMP01 ADROP TABLE #TABLENAMEDROP TABLE #TEMP01/*(10 行受影响)(10 行受影响)
ID                   fitem      fdata                   fbillno    fbegqty                                 fqty                                    fendqty                                 
-------------------- ---------- ----------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1                    A01        2013-01-01 00:00:00.000 Win001     100.00                                  70.00                                   0.00                                    170.00
2                    A01        2013-01-01 00:00:00.000 Win002     100.00                                  20.00                                   0.00                                    190.00
3                    A01        2013-01-03 00:00:00.000 Out001     100.00                                  -30.00                                  0.00                                    160.00
1                    A02        2013-01-02 00:00:00.000 win003     200.00                                  80.00                                   0.00                                    280.00
2                    A02        2013-01-03 00:00:00.000 win004     200.00                                  60.00                                   0.00                                    340.00
3                    A02        2013-01-04 00:00:00.000 Out002     200.00                                  -60.00                                  0.00                                    280.00
4                    A02        2013-01-04 00:00:00.000 Out003     200.00                                  -10.00                                  0.00                                    270.00
1                    A03        2013-01-04 00:00:00.000 win005     90.00                                   8.00                                    0.00                                    98.00
2                    A03        2013-01-04 00:00:00.000 win006     90.00                                   9.00                                    0.00                                    107.00
3                    A03        2013-01-04 00:00:00.000 Out004     90.00                                   6.00                                    0.00                                    113.00(10 行受影响)
*/