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,如何进行更新
------解决方案--------------------
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.0000000000
SELECT 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 A
DROP TABLE #TABLENAME
DROP TABLE #TEMP01
/*
(10 行受影响)
(10 行受影响)
ID fitem fdata fbillno fbegqty fqty fendqty