java - SQLiteDB 中大量数字丢失精度？

Question

我将 0.00 个十进制值作为文本存储在 SQLiteDB 中。我正在通过以下方式检索它们

"SELECT SUM(SUBSTR(amount, 1)) FROM record WHERE date BETWEEN '" +
    date1 + "' AND '" + date2 + "'";

问题是当数字开始达到 10000.00 时，精度似乎出现问题。

Answer 1

该值已转换为 REAL(存在浮点问题)。一种选择是将其四舍五入

"SELECT ROUND(SUM(SUBSTR(amount, 1)),2) FROM record WHERE date BETWEEN '"+date+"' AND '"+date2+"'";

或者使用 INTEGER 数学，将位置移动 2 位小数

"SELECT SUM(CAST(SUBSTR(amount, 1)*100 as INT))/100.0 FROM record WHERE date BETWEEN '"+date+"' AND '"+date2+"'";

http://www.sqlite.org/datatype3.html

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes: NULL, INTEGER, REAL, TEXT, BLOB

What you need to know about Floating Point maths