求一个应用问题的方法
fitem fdata fbillno fbegqty fqty fendqty
A012013-01-01 00:00:00.000Win001100.000000000070.00000000000.0000000000
A012013-01-01 00:00:00.000Win002100.000000000020.00000000000.0000000000
A012013-01-03 00:00:00.000Out001100.0000000000-30.00000000000.0000000000
A022013-01-02 00:00:00.000win003200.000000000080.00000000000.0000000000
A022013-01-03 00:00:00.000win004200.000000000060.00000000000.0000000000
A022013-01-04 00:00:00.000Out002200.0000000000-60.00000000000.0000000000
A022013-01-04 00:00:00.000Out003200.0000000000-10.00000000000.0000000000
A032013-01-04 00:00:00.000win00590.00000000008.00000000000.0000000000
A032013-01-04 00:00:00.000win00690.00000000009.00000000000.0000000000
A032013-01-04 00:00:00.000Out00490.00000000006.00000000000.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
-------------------- ---------- ----------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
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 行受影响)
*/
