急希望能用最优的sql语句来实现解决思路
急!急!急!希望能用最优的sql语句来实现
表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万,所以请希望能有最优的代码来实现
------解决方案--------------------
表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 ALL SELECT '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_n AS /* EXEC test_up_a_n */ BEGIN DECLARE @Id INT, @h VARCHAR(2), @h2 VARCHAR(2), @l INT, @y NUMERIC(3,1), @z INT, @a_y NUMERIC(3,1), @flag INT DECLARE @sum_y NUMERIC(3,1), @save_id INT SET @sum_y=0 CREATE 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 @MyData FETCH NEXT FROM @MyData INTO @Id, @h, @l, @y, @z, @a_y WHILE @@FETCH_STATUS = 0 BEGIN IF(@h2<>@h) --@h发生变化时,将@sum_y清零,从头开始累加计数 BEGIN SET @h2=@h SET @sum_y=0 END IF(@sum_y+@y<=@a_y) --@sum_y累加@y后,不会超出A表中的y值时,累加@y给@sum_y BEGIN INSERT INTO #n_back(Id, h, l, y, z) VALUES(@Id, @h, @l, @y, @z) SET @sum_y=@sum_y+@y END ELSE --@sum_y累加@y后,将要超出A表中的y值时,标记记录行,此次@sum_y不累加@y BEGIN UPDATE #n_temp1 SET flag=1 WHERE Id=@Id END FETCH NEXT FROM @MyData INTO @Id, @h, @l, @y, @z, @a_y END 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_y FROM #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=1 INSERT INTO #n_back(Id, h, l, y, z) SELECT Id, h, l, a_y-ISNULL(sum_y,0),z FROM #n n WHERE n.Id IN ( SELECT MIN(ID) FROM #n GROUP BY h ); UPDATE #n SET 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 h DROP TABLE #n_temp1, #n_back, #n; END