sqlsever存储过程问题

Question

在SQLSEVER中如何用存储过程把图片中的左表数据qty列按chaifenliang列拆分，得到如下图右表

Answer 1

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
可以通过以下存储过程把图片中的左表数据qty列按chaifenliang列拆分，得到右表：

CREATE PROCEDURE SplitQtyByChaifenliang
AS
BEGIN
    CREATE TABLE #TempData
    (
        id INT,
        chaifenliang FLOAT,
        qty FLOAT,
        dataIndex INT
    )
    
    INSERT INTO #TempData
    SELECT id, chaifenliang, qty, ROW_NUMBER() OVER(ORDER BY id) AS dataIndex
    FROM leftTable
    
    DECLARE @rowCount INT
    SET @rowCount = @@ROWCOUNT
    
    DECLARE @currentIndex INT
    SET @currentIndex = 1
    
    WHILE @currentIndex <= @rowCount
    BEGIN
        DECLARE @currentId INT
        SET @currentId = (SELECT id FROM #TempData WHERE dataIndex = @currentIndex)
        
        DECLARE @currentQty FLOAT
        SET @currentQty = (SELECT qty FROM #TempData WHERE dataIndex = @currentIndex)
        
        DECLARE @currentChaifenliang FLOAT
        SET @currentChaifenliang = (SELECT chaifenliang FROM #TempData WHERE dataIndex = @currentIndex)

        WHILE @currentQty > 0
        BEGIN
            DECLARE @insertQty FLOAT
            
            IF @currentQty <= @currentChaifenliang
            BEGIN
                SET @insertQty = @currentQty
                SET @currentQty = 0
            END
            ELSE
            BEGIN
                SET @insertQty = @currentChaifenliang
                SET @currentQty = @currentQty - @currentChaifenliang
            END
            
            INSERT INTO rightTable(id, chaifenliang, qty)
            VALUES(@currentId, @currentChaifenliang, @insertQty)
        END
        
        SET @currentIndex = @currentIndex + 1
    END
    
    DROP TABLE #TempData
END

需要把代码中的leftTable和rightTable替换为实际使用的表名。
如果我的回答解决了您的问题，请采纳！