日期:2014-05-17  浏览次数:20449 次

求一个应用问题的方法
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     &nb