数据多次分摊方案二

最新推荐文章于 2024-07-19 14:12:49 发布

NO66781

最新推荐文章于 2024-07-19 14:12:49 发布

阅读量389

点赞数

分类专栏：浪潮GS MSSQL

本文链接：https://blog.csdn.net/NO66781/article/details/109882631

版权

MSSQL 同时被 2 个专栏收录

17 篇文章 1 订阅

订阅专栏

浪潮GS

9 篇文章 3 订阅

订阅专栏

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

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