資料多次分攤方案二

方案一有很多的不足，優化以後效率提升很多

DECLARE @GSID VARCHAR(5)
SET @GSID ='01001'

/*拼接資料*/
CREATE TABLE #THZ(
    ID INT NOT NULL IDENTITY(1,1)
   ,GSID  VARCHAR(5)  NULL DEFAULT ''
   ,KH  VARCHAR(40) NULL DEFAULT ''
   ,WL  VARCHAR(40) NULL DEFAULT ''
   ,PH  VARCHAR(40) NULL DEFAULT ''
   ,DJ  DECIMAL(20,8) NULL DEFAULT 0.00000000
   ,SL DECIMAL(14,2) NULL DEFAULT 0.00 
)
INSERT INTO #THZ (GSID,KH,WL,PH,DJ,SL)
SELECT '01001','01001-JZT166','01001-JQ000005','190220',1.00000000,10.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',2.00000000,2.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.00000000,300.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.10000000,12.00 UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.20000000,12.00 UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.30000000,1.00  UNION ALL 
SELECT '01001','01001-JZT166','01001-JQ000005','190220',3.40000000,2.00  UNION ALL 
SELECT '01001','01001-JZT167','01001-JQ000005','190220',3.50000000,3.00  

CREATE TABLE #LX_SJ(
    ID INT NOT NULL IDENTITY(1,1)
   ,GSID  VARCHAR(5)  NULL DEFAULT ''
   ,NY VARCHAR(6) NULL DEFAULT ''
   ,LSBH  VARCHAR(40) NULL DEFAULT ''
   ,FLBH  VARCHAR(40) NULL DEFAULT ''
   ,YWRQ  VARCHAR(40) NULL DEFAULT ''
   ,KH  VARCHAR(40) NULL DEFAULT ''
   ,WL  VARCHAR(40) NULL DEFAULT ''
   ,PH VARCHAR(40) NULL DEFAULT ''
   ,SL DECIMAL(14,2) NULL DEFAULT 0.00 
   ,SYSL DECIMAL(14,2) NULL DEFAULT 0.00 
)
INSERT INTO #LX_SJ (GSID,NY,LSBH,FLBH,YWRQ,KH,WL,PH,SL)
SELECT '01001','202001','No0001','0001','20200116','01001-JZT166','01001-JQ000005','190220',3.00 UNION ALL 
SELECT '01001','202001','No0002','0001','20200107','01001-JZT166','01001-JQ000005','190220',20.00 UNION ALL 
SELECT '01001','202001','No0003','0001','20200107','01001-JZT166','01001-JQ000005','190220',3.00 UNION ALL 
SELECT '01001','202001','No0004','0001','20200106','01001-JZT166','01001-JQ000005','190220',20.00 UNION ALL 
SELECT '01001','202001','No0005','0001','20200116','01001-JZT167','01001-JQ000005','190220',8.00 UNION ALL 
SELECT '01001','202001','No0006','0001','20200107','01001-JZT167','01001-JQ000005','190220',20.00 UNION ALL 
SELECT '01001','202001','No0007','0001','20200107','01001-JZT167','01001-JQ000005','190220',3.00 UNION ALL 
SELECT '01001','202001','No0008','0001','20200106','01001-JZT167','01001-JQ000005','190220',5.00 


SELECT * FROM #THZ
--SELECT * FROM #LX_SJ WHERE KH ='01001-JZT166' ORDER BY GSID,NY,KH,WL,PH,YWRQ

CREATE TABLE #LX_SJFT(
    ID INT NOT NULL 
   ,XGID INT 
   ,GSID  VARCHAR(5)  NULL DEFAULT ''
   ,NY VARCHAR(6) NULL DEFAULT ''
   ,LSBH  VARCHAR(40) NULL DEFAULT ''
   ,FLBH  VARCHAR(40) NULL DEFAULT ''
   ,YWRQ  VARCHAR(40) NULL DEFAULT ''
   ,KH  VARCHAR(40) NULL DEFAULT ''
   ,WL  VARCHAR(40) NULL DEFAULT ''
   ,PH VARCHAR(40) NULL DEFAULT ''
   ,DJ  DECIMAL(20,8) NULL DEFAULT 0.00000000
   ,SL DECIMAL(14,2) NULL DEFAULT 0.00
   ,UPSL DECIMAL(14,2) NULL DEFAULT 0.00
   ,FTSL DECIMAL(14,2) NULL DEFAULT 0.00
)
 

DECLARE @FT_KH VARCHAR(40)
       ,@FT_WL VARCHAR(40)
       ,@FT_PH VARCHAR(40)
       ,@FT_DJ DECIMAL(20,8)
       ,@FT_SL DECIMAL(14,2) 

/*定義遊標*/
DECLARE CURSOR_LXFT CURSOR FOR  

SELECT KH,WL,PH,DJ,SL FROM #THZ WHERE SL <> 0 FOR READ ONLY

/*開啟遊標*/
OPEN CURSOR_LXFT

/*指標下移*/
FETCH NEXT FROM CURSOR_LXFT INTO @FT_KH,@FT_WL,@FT_PH,@FT_DJ,@FT_SL
WHILE @@fETCH_STATUS = 0
BEGIN 
	
	INSERT INTO #LX_SJFT (ID,XGID,GSID,NY,LSBH,FLBH,YWRQ,KH,WL,PH,DJ,SL,UPSL,FTSL)
	SELECT ROW_NUMBER() OVER(ORDER BY a.YWRQ ASC) AS ID,a.ID AS XGID,a.GSID,a.NY,a.LSBH,a.FLBH,a.YWRQ,a.KH,a.WL,a.PH,@FT_DJ,a.SL - ISNULL(a.SYSL,0.00),@FT_SL,0 
	FROM #LX_SJ AS a 
	WHERE a.GSID = @GSID AND a.KH =@FT_KH AND a.WL = @FT_WL AND a.PH =@FT_PH  AND a.SL - ISNULL(a.SYSL,0.00) > 0
	ORDER BY a.YWRQ ASC 
    IF (SELECT COUNT(*) FROM #LX_SJFT) > 0
    BEGIN 
		DECLARE @TMP_UPSL DECIMAL(14,2)  /*分攤結果*/
			   ,@TMP_FTSL DECIMAL(14,2)  /*需要分攤的數量*/
		
		SET @TMP_UPSL = 0
		/*取值*/
		SELECT TOP 1 @TMP_FTSL = UPSL FROM #LX_SJFT
	 
		IF EXISTS(SELECT 1 FROM #LX_SJFT WHERE SL >= UPSL)
		BEGIN
			UPDATE #LX_SJFT
				SET @TMP_FTSL = @TMP_FTSL - @TMP_UPSL
				   ,@TMP_UPSL = CASE WHEN SL >=  @TMP_FTSL THEN @TMP_FTSL ELSE 0 END 
				   ,FTSL = @TMP_UPSL
		END 
		ELSE
		BEGIN
			UPDATE #LX_SJFT
				SET @TMP_FTSL    = @TMP_FTSL - @TMP_UPSL
				   ,@TMP_UPSL = CASE WHEN SL >=  @TMP_FTSL THEN @TMP_FTSL ELSE CASE WHEN SL > 0 THEN SL ELSE 0 END END 
				   ,FTSL = @TMP_UPSL
			
		END 
		
		UPDATE a
		SET a.SYSL = ISNULL(a.SYSL,0.00) + ISNULL(b.FTSL,0.00)
		FROM #LX_SJ AS a
			 INNER JOIN #LX_SJFT AS b ON a.ID = b.XGID     
		WHERE b.FTSL > 0
    
    END 
	/*刪除臨時表*/
	DELETE #LX_SJFT
	/*指標下移*/
	FETCH NEXT FROM CURSOR_LXFT INTO @FT_KH,@FT_WL,@FT_PH,@FT_DJ,@FT_SL
END 
/*關閉遊標*/
CLOSE CURSOR_LXFT

/*清除遊標*/
DEALLOCATE CURSOR_LXFT
 

DROP TABLE #THZ
DROP TABLE #LX_SJ
DROP TABLE #LX_SJFT