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