具有相同值的SQLite计算结果不同

问题描述

我的表包含此值

productName | purchasePrice | tax | price
------------+---------------+-----+--------
Product 1   | 5099          | 16  | 10099
Product 1   | 5099          | 16  | 10099
Product 1   | 5099          | 16  | 10099
Product 1   | 5099          | 16  | 10099
Product 2   | 5099          | 19  | 10099
Product 2   | 5099          | 19  | 10099
Product 2   | 5099          | 19  | 10099

我对总利润的计算是

SUM( price- ( price * tax/100.0  + purchasePrice)) as Profit

利润结果= 22780.210000000006

我对每种产品的利润的计算是

My Calculation for the Profit of every Product is this

SUM(price- (price*TAX/100.0 + purchasePrice)) as Profit GROUP BY productName

产品1的利润结果= 13536,6
产品2的利润结果= 9243,57

Result as Profit for Product 1 = 13536,6
Result as Profit for Product 2 = 9243,57

总 22780,17

我必须舍入此值并将它们除以/100.0，因为我读到最好不要在Sqlite中存储带浮点的值.

I have to round this Values and divide them by /100.0 because I've read that it is better to not store the values with floating points in Sqlite.

我像这样进行四舍五入和/100.0

I do the rounding and /100.0 like this

舍入为总数，结果为= 227.8

ROUND((SUM( price- ( price * tax/100.0  + purchasePrice)))/100 ,2) as Profit

每件商品的回合

ROUND((SUM( price- ( price * tax/100.0  + purchasePrice)))/100 ,2) as Profit GROUP BY productName

产品1的结果= 135,37
产品2结果= 92,44

Product 1 result = 135,37
Product 2 result = 92,44

总计 227,81 ，但总价舍入给我的结果是= 227.8

Total 227,81 but the Round for Total gives me the Result of = 227.8

有什么想法吗?

推荐答案

在最新版的SQL-lite中，以下代码适用于Round

In the latest version of SQL-lite the below code works with Round

select SUM(Profit) from (
select productName,round((SUM(price- (price*TAX/100.0 + purchasePrice)))/100 ,2)
as Profit
from test
GROUP BY productName
)x

http://sqlfiddle.com/#!5/8eba1/25

这篇关于具有相同值的SQLite计算结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！