用存储过程吧

ALTER procedure Proc_Test

AS

BEGIN

SET NOCOUNT ON;

DECLARE @NAME_ NVARCHAR(100),@RQ_ DATE,@TOTAL_ FLOAT,@CN_ FLOAT,@I FLOAT

/*创建测试表，正式环境不用创建*/

IF OBJECT_ID('TEMPDB..#Table_OLD') is NOT null

DROP TABLE  #Table_OLD

CREATE TABLE #Table_OLD(

[产品名称] [nvarchar](50)NULL,

[交期][DATE] NULL,

[订单数量][float] NULL,

[日产能][float] NULL

) 

/*创建保存结果的临时表*/

IF OBJECT_ID('TEMPDB..#Table_NEW') is NOT null

DROP TABLE  #Table_NEW

CREATE TABLE #Table_NEW(

[产品名称] [nvarchar](50)NULL,

[日期][DATE] NULL,

[生产数量][float] NULL,

[订单数量][float] NULL,

[日产能][float] NULL

)

/*创建测试数据*/

INSERT INTO #Table_OLD VALUES('AAA','2022-03-09',10000,2000)

INSERT INTO #Table_OLD VALUES('BBB','2022-03-12',17000,2000)

/*遍历测试数据*/

DECLARE MY_CUR CURSOR

FOR SELECT 产品名称,交期,订单数量,日产能 FROM #Table_OLD

OPEN MY_CUR

FETCH NEXT FROM MY_CUR INTO @NAME_,@RQ_,@TOTAL_,@CN_

WHILE @@FETCH_STATUS = 0

BEGIN

SET @I=@TOTAL_

SET @RQ_=DATEADD(DAY,-1,@RQ_)

/*循环拆分日产能*/

WHILE @I>0 BEGIN

INSERT INTO #Table_NEW VALUES (''+CONVERT(NVARCHAR(100),@NAME_)+'',''+CONVERT(NVARCHAR(100),@RQ_)+'',''+CONVERT(NVARCHAR(100),@CN_)+'',''+CONVERT(NVARCHAR(100),@TOTAL_)+'',''+CONVERT(NVARCHAR(100),@CN_)+'')

SET @RQ_=DATEADD(DAY,-1,@RQ_)

SET @I=@I-@CN_

END

FETCH NEXT FROM MY_CUR INTO @NAME_,@RQ_,@TOTAL_,@CN_

END

CLOSE MY_CUR

DEALLOCATE MY_CUR

SELECT * FROM #Table_NEW

END