SQLSERVER 从大到小递减算法

来源：互联网发布：php 工作日志管理系统编辑：程序博客网时间：2024/06/08 08:26

生成算法公式：@incomeAmount*16%-@incomeAmount*(16-8)%-@incomeAmount*(8-3)%说明：按Level从大到小递减；@incomeAmount为自定义系数，默认20.8结果：20.8*16*0.01-20.8*-8*0.01-20.8*-5*0.01

DECLARE @incomeAmount FLOAT=20.8;WITH dt AS(SELECT Level=1,ConsignSale=3UNION ALLSELECT Level=2,ConsignSale=8UNION ALLSELECT Level=3,ConsignSale=16)SELECT ROW_NUMBER() OVER(ORDER BY LEVEL DESC) RowIndex,* INTO #dt FROM dtDECLARE @Count INT,@RowIndex INT=1,@PrevConsignSale FLOAT=0,@ExcSQL VARCHAR(800)=''SELECT @Count=COUNT(*) FROM #dtWHILE(@Count>0)BEGINIF @RowIndex=1BEGINSELECT @PrevConsignSale=[ConsignSale] FROM #dt WHERE RowIndex=@RowIndexSET @ExcSQL=CONVERT(VARCHAR(100),@incomeAmount)+'*'+ CONVERT(VARCHAR(100),@PrevConsignSale) +'*0.01' ENDELSEBEGINSELECT @ExcSQL=@ExcSQL+'-'+CONVERT(VARCHAR(100),@incomeAmount)+'*'+ CONVERT(VARCHAR(100),[ConsignSale]-@PrevConsignSale) +'*0.01' FROM #dt WHERE RowIndex=@RowIndexSELECT @PrevConsignSale=[ConsignSale] FROM #dt WHERE RowIndex=@RowIndexENDSET @RowIndex=@RowIndex+1SET @Count=@Count-1ENDCREATE TABLE #rs(Success FLOAT)INSERT INTO #rs(Success)EXEC('select Success='+@ExcSQL)SELECT * FROM #dtSELECT * FROM #rsSELECT [计算公式]=(@ExcSQL)DROP TABLE #dt,#rs

阅读全文

0 0