用游标将下列左边数据拆分成右边数据

QTY列根据BILI列拆分成右表，如QTY为1000，BILI为500就拆分成500，500，QTY为1100，BILI为500，就拆分成500，500，100

可以借鉴下

 
-- 创建存储过程
CREATE PROCEDURE split_qty_by_bili
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cur_id INT, @cur_ustr VARCHAR(255), @cur_qty INT, @cur_bili INT, @qty_remain INT, @qty_split INT;
    DECLARE cur CURSOR FOR SELECT ID, USTR, QTY, BILI FROM table1;
    DROP TABLE IF EXISTS table2;
    CREATE TABLE table2 (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        USTR VARCHAR(255),
        QTY INT,
        BILI INT
    );
    OPEN cur;
    FETCH NEXT FROM cur INTO @cur_id, @cur_ustr, @cur_qty, @cur_bili;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @qty_remain = @cur_qty;
        WHILE @qty_remain > 0
        BEGIN
            IF @qty_remain >= @cur_bili
                SET @qty_split = @cur_bili;
            ELSE
                SET @qty_split = @qty_remain;
            INSERT INTO table2 (USTR, QTY, BILI) VALUES (@cur_ustr, @qty_split, @cur_bili);
            SET @qty_remain = @qty_remain - @qty_split;
        END;
        FETCH NEXT FROM cur INTO @cur_id, @cur_ustr, @cur_qty, @cur_bili;
    END;
    CLOSE cur;
    DEALLOCATE cur;
END;
GO
 

这是sql sever版本，我不知道你为啥要用游标执行效率有打开看过？非常低，我用Set-based和 CROSS APPLY作替代游标：

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE split_qty_by_bili()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur_ustr VARCHAR(255);
    DECLARE cur_qty INT;
    DECLARE cur_bili INT;
    DECLARE qty_remain INT;
    DECLARE qty_split INT;
    DECLARE cur CURSOR FOR SELECT ID, USTR, QTY, BILI FROM table1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DROP TABLE IF EXISTS table2;
    CREATE TABLE table2 (
        ID INT AUTO_INCREMENT PRIMARY KEY,
        USTR VARCHAR(255),
        QTY INT,
        BILI INT
    );
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO cur_id, cur_ustr, cur_qty, cur_bili;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET qty_remain = cur_qty;
        WHILE qty_remain > 0 DO
            IF qty_remain >= cur_bili THEN
                SET qty_split = cur_bili;
            ELSE
                SET qty_split = qty_remain;
            END IF;
            INSERT INTO table2 (USTR, QTY, BILI) VALUES (cur_ustr, qty_split, cur_bili);
            SET qty_remain = qty_remain - qty_split;
        END WHILE;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-》〉》〉这是cross apply替代写法；


CREATE PROCEDURE split_qty_by_bili
AS
BEGIN
SET NOCOUNT ON;

DROP TABLE IF EXISTS table2;
CREATE TABLE table2 (
ID INT IDENTITY(1,1) PRIMARY KEY,
USTR VARCHAR(255),
QTY INT,
BILI INT
);

INSERT INTO table2 (USTR, QTY, BILI)
SELECT t1.USTR,
CASE
WHEN t1.QTY >= t1.BILI THEN t1.BILI
ELSE t1.QTY
END AS QTY,
t1.BILI
FROM (
SELECT ID, USTR, QTY, BILI
FROM table1
) AS t1
CROSS APPLY (
SELECT TOP (t1.QTY / t1.BILI + CASE WHEN t1.QTY % t1.BILI > 0 THEN 1 ELSE 0 END)
1 AS n
FROM sys.objects AS o1
CROSS JOIN sys.objects AS o2
) AS ca;

END;

直接 cross master..spt_values然后算一下就可以了

with t as (
    select 1 id,'A' ustr,1000 qty,500 bili
    union all select 2,'B',1100,500
    union all select 3,'C',1400,600
    union all select 4,'D',1500,400
),t1 as (
    select * from t
    cross apply(
        select number from master..spt_values where type='p' and number <= (qty - 1)/bili
    ) b
)
select *
    ,(case when (number+1) * bili < qty then bili else qty - number * bili end) nqty
from t1
order by id,number

可以借鉴下

 
-- 创建存储过程
CREATE PROCEDURE split_qty_by_bili
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cur_id INT, @cur_ustr VARCHAR(255), @cur_qty INT, @cur_bili INT, @qty_remain INT, @qty_split INT;
    DECLARE cur CURSOR FOR SELECT ID, USTR, QTY, BILI FROM table1;
    DROP TABLE IF EXISTS table2;
    CREATE TABLE table2 (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        USTR VARCHAR(255),
        QTY INT,
        BILI INT
    );
    OPEN cur;
    FETCH NEXT FROM cur INTO @cur_id, @cur_ustr, @cur_qty, @cur_bili;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @qty_remain = @cur_qty;
        WHILE @qty_remain > 0
        BEGIN
            IF @qty_remain >= @cur_bili
                SET @qty_split = @cur_bili;
            ELSE
                SET @qty_split = @qty_remain;
            INSERT INTO table2 (USTR, QTY, BILI) VALUES (@cur_ustr, @qty_split, @cur_bili);
            SET @qty_remain = @qty_remain - @qty_split;
        END;
        FETCH NEXT FROM cur INTO @cur_id, @cur_ustr, @cur_qty, @cur_bili;
    END;
    CLOSE cur;
    DEALLOCATE cur;
END;
GO
 

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
左表：

右表：

代码如下：

import pyodbc

# 连接数据库
cnxn = pyodbc.connect(
    'DRIVER={SQL Server};SERVER=your_server_name;DATABASE=your_database_name;UID=your_username;PWD=your_password')

# 新建游标
cursor = cnxn.cursor()

# 查询语句
sql_select = 'SELECT PRODUCT, QTY, BILI FROM your_table_name'

# 插入语句
sql_insert = 'INSERT INTO your_table_name (PRODUCT, QTY) VALUES (?, ?)'

# 执行查询
cursor.execute(sql_select)

# 遍历查询结果
for row in cursor.fetchall():
    # 判断BILI是否大于QTY
    if row[2] > row[1]:
        # 如果大于，直接插入原始数据
        cursor.execute(sql_insert, (row[0], row[1]))
    else:
        # 如果小于或等于，计算需要拆分几次
        split_times = row[1] // row[2]
        # 计算余数
        remainder = row[1] % row[2]
        # 循环插入拆分后的数据
        for i in range(split_times):
            cursor.execute(sql_insert, (row[0], row[2]))
        # 如果有余数，再插入一条数据
        if remainder > 0:
            cursor.execute(sql_insert, (row[0], remainder))

# 提交事务
cnxn.commit()

# 关闭游标和连接
cursor.close()
cnxn.close()

需要根据实际表结构和参数进行调整。
如果我的回答解决了您的问题，请采纳！