商品金额折让遇到的坑

数据库:SQLSERVER 

1,不废话,先贴上几个数据的运算

SELECT 474.3000-FLOOR(140.0000*474.3000/1352.5000)

SELECT 439.1000-FLOOR(140.0000*439.1000/1352.5000)

SELECT 439.1000-FLOOR(140.0000-94.0000)

 

计算后的结果分别是  425.3000　　394.1000　　393.1000

然后下面放一段SQL的测试

 

--创建了一个表

CREATE TABLE Test_99(
id INT,
JE NUMERIC(18,4)
)

--插入3条数据

INSERT INTO Test_99 VALUES(1,474.3000)
INSERT INTO Test_99 VALUES(2,439.1000)
INSERT INTO Test_99 VALUES(3,439.1000)

DECLARE @index INT=1

DECLARE @ALLJSJE NUMERIC(18,4)=140.0000

DECLARE @TMPJE NUMERIC(18,4)=0
DECLARE @ALLMXJE NUMERIC(18,4)=(SELECT SUM(JE) FROM Test_99)
WHILE @index<=3
BEGIN
DECLARE @JE NUMERIC(18,4)=(SELECT JE FROM Test_99 WHERE id=@index)
IF @index=3
BEGIN
UPDATE Test_99 SET JE=CASE WHEN JE-FLOOR(@ALLJSJE-@TMPJE)<=0 THEN 0 ELSE JE-FLOOR(@ALLJSJE-@TMPJE) END WHERE id=@index
END
ELSE
BEGIN
UPDATE Test_99 SET JE=CASE WHEN JE-FLOOR(@ALLJSJE*@JE/@ALLMXJE)<=0 THEN 0 ELSE JE-FLOOR(@ALLJSJE*@JE/@ALLMXJE) END WHERE id=@index
END
SET @index+=1
SET @TMPJE=@TMPJE+FLOOR(@ALLJSJE*@JE/@ALLMXJE)
END

运行了上面的运算之后发现  结果好像哪里不对

 

 

 

哪里出了问题? 哪里出了问题? 哪里出了问题?

一顿操作之后证明是在 NUMERIC-FLOOR  类型运算上面出了问题

 

下面贴上没有问题的代码

DECLARE @index INT=1
DECLARE @ALLJSJE NUMERIC(18,4)=140.0000
DECLARE @TMPJE NUMERIC(18,4)=0
DECLARE @ALLMXJE NUMERIC(18,4)=(SELECT SUM(JE) FROM Test_99)
WHILE @index<=3
BEGIN
DECLARE @JE NUMERIC(18,4)=(SELECT JE FROM Test_99 WHERE id=@index)
IF @index=3
BEGIN
UPDATE Test_99 SET JE=CASE WHEN JE-FLOOR(@ALLJSJE-@TMPJE)<=0 THEN 0 ELSE JE-FLOOR(@ALLJSJE-@TMPJE) END WHERE id=@index
END
ELSE
BEGIN
DECLARE @JE1 NUMERIC(18,4)=FLOOR(@ALLJSJE*@JE/@ALLMXJE)
UPDATE Test_99 SET JE=CASE WHEN JE-@JE1<=0 THEN 0 ELSE JE-@JE1 END WHERE id=@index
END
SET @index+=1
SET @TMPJE=@TMPJE+FLOOR(@ALLJSJE*@JE/@ALLMXJE)
END

请看标红的地方的代码

计算的结果

 

 

此方法适用于商品结算时的视同折让算法

例如: 3件商品总共折扣了5块钱,如果算法是要取整的话,那么每件商品折扣的钱就是1,1,3

如果金额精确到角的话,那么折扣到三件商品的金额就是1.6 , 1.6  ,1.8