表A中的数据为32条,表N中的数据为17万条
如果
A表里面的记录为
h,Y
A 1.7
B 0.1
n表里面的字段有
h l y z
A 2 0.1 222
A 3 0.5 333
A 4 0.6 111
A 22 0.8 666
B 5 1 555
B 11 0.2 777
得到的结果为
n_back表中
h l y z
A 2 0.1 222
A 22 0.8 666
A 4 0.6 111
A 3 0.2 333
B 5 0.1 555
n表中
h l y z
B 5 0.9 555
A 3 0.3 333
B 11 0.2 777
A表有字段h和y,h是一个字符型 y是一个浮点型，A是一个总表,n表是A表的一个明细表，是将A与n表中h字段相同的数据移到n_ngkc表中，但是数据要管制同一个h字段的累计y的量不能大于A表中y的量，只能是小于或等于,大于部份存在n表中，因为n表中的数据量较多，有17万，所以请希望能有最优的代码来实现
------解决方案--------------------
- SQL code
CREATE TABLE a(h VARCHAR(2), y NUMERIC(3,1));INSERT INTO a(h,y)SELECT 'A', 1.7 UNION ALLSELECT 'B', 0.1;CREATE TABLE n(h VARCHAR(2), l INT, y NUMERIC(3,1), z INT);INSERT INTO n(h,l,y,z)SELECT'A',2,0.1,222 UNION ALL SELECT'A',3,0.5,333 UNION ALL SELECT'A',4,0.6,111 UNION ALL SELECT 'A',22,0.8,666 UNION ALL SELECT 'B',5,1,555 UNION ALL SELECT 'B',11,0.2,777; CREATE TABLE n_back(h VARCHAR(2), l INT, y NUMERIC(3,1), z INT);------------------------------------------------ALTER PROC test_up_a_nAS/*EXEC test_up_a_n*/BEGINDECLARE @Id INT, @h VARCHAR(2), @h2 VARCHAR(2), @l INT, @y NUMERIC(3,1), @z INT, @a_y NUMERIC(3,1), @flag INTDECLARE @sum_y NUMERIC(3,1), @save_id INTSET @sum_y=0CREATE TABLE #n_temp1(Id INT IDENTITY(1,1), h VARCHAR(2), l INT, y NUMERIC(3,1), z INT, a_y NUMERIC(3,1), flag INT DEFAULT 0);INSERT INTO #n_temp1(h, l, y, z, a_y)SELECT n.h, n.l, n.y, n.z, a.y FROM n LEFT JOIN a ON a.h=n.h ORDER BY n.h ASC, n.y DESC;CREATE TABLE #n_back(Id INT, h VARCHAR(2), l INT, y NUMERIC(3,1), z INT);--游标检索DECLARE @MyData CURSOR SET @MyData = CURSOR FOR SELECT Id, h, l, y, z, a_y, flag from #n_temp1;OPEN @MyDataFETCH NEXT FROM @MyData INTO @Id, @h, @l, @y, @z, @a_yWHILE @@FETCH_STATUS = 0 BEGIN IF(@h2<>@h) [email protected]，[email protected]_y清零，从头开始累加计数 BEGIN SET @[email protected] SET @sum_y=0 END IF(@[email protected]<[email protected]_y) [email protected][email protected]，不会超出A表中的y值时，[email protected]@sum_y BEGIN INSERT INTO #n_back(Id, h, l, y, z) VALUES(@Id, @h, @l, @y, @z) SET @[email protected][email protected] END ELSE [email protected][email protected]，将要超出A表中的y值时，标记记录行，[email protected][email protected] BEGIN UPDATE #n_temp1 SET flag=1 WHERE [email protected] END FETCH NEXT FROM @MyData INTO @Id, @h, @l, @y, @z, @a_yEND CLOSE @MyData DEALLOCATE @MyData CREATE TABLE #n(Id INT, h VARCHAR(2), l INT, y NUMERIC(3,1), z INT, a_y NUMERIC(3,1),sum_y NUMERIC(3,1));INSERT INTO #n(Id, h, l, y, z, a_y, sum_y)SELECT a.Id, a.h, a.l, a.y, a.z, a.a_y, b.sum_yFROM #n_temp1 a LEFT JOIN (SELECT h, SUM(y) sum_y FROM #n_back GROUP BY h) b ON a.h=b.h WHERE a.flag=1INSERT INTO #n_back(Id, h, l, y, z)SELECT Id, h, l, a_y-ISNULL(sum_y,0),zFROM #n nWHERE n.Id IN ( SELECT MIN(ID) FROM #n GROUP BY h );UPDATE #nSET y=(y-(a_y-ISNULL(sum_y,0)))WHERE ID IN ( SELECT MIN(ID) FROM #n GROUP BY h );SELECT h, l, y, z FROM #n_back order by Id;SELECT h, l, y, z FROM #n order by hDROP TABLE #n_temp1, #n_back, #n;END