问题描述
我有一个 SQLite3 表，其中的列格式为 DECIMAL(7,2)，但是每当我选择的行的值不具有非零第二位小数位(例如 3.00 或 3.10)时，结果总是带有尾随零(s) 缺失(例如 3 或 3.1).有什么方法可以在 SELECT 语句中应用格式化函数，以便获得所需的 2dp?我试过 ROUND()，但这没有效果.否则，每次执行 SELECT 语句时，我都必须不断将结果列值转换为所需的显示格式(在我的情况下使用 Python)，这真的很痛苦.
I have a SQLite3 table with a column having format DECIMAL(7,2), but whenever I select rows with values not having a non-zero 2nd decimal place (eg. 3.00 or 3.10), the result always has trailing zero(s) missing (eg. 3 or 3.1). Is there any way that I can apply a formatting function in the SELECT statement so that I get the required 2dp? I have tried ROUND(), but this has no effect. Otherwise I have to keep converting the resulting column values into the required format for display (using Python in my case) every time I do a SELECT statement, which is a real pain.
我什至不介意结果是字符串而不是数字，只要它有正确的小数位数即可.
I don't even mind if the result is string instead of numeric, as long as it has the right number of decimal places.
任何帮助将不胜感激.
艾伦
推荐答案
SQLite 在内部使用 IEEE 二进制浮点运算，这确实不太适合维护特定的小数位数.要获得这种类型的十进制处理需要以下之一:
SQLite internally uses IEEE binary floating point arithmetic, which truly does not lend itself well to maintaining a particular number of decimals. To get that type of decimal handling would require one of:
- 定点数学，或
- IEEE 十进制浮点数(不常见)，或
- 将所有内容作为字符串处理.
在提取后格式化值(从浮点数转换为字符串)是实现事物的最简单方法.您甚至可以将其隐藏在某种包装器中，以便其余代码不必处理后果.但是如果您之后要对值进行算术运算，那么最好不要格式化，而是使用查询返回的值，因为格式并重新转换回二进制浮点数(Python 使用的，就像绝大多数其他现代语言一样)由于精度降低而丢失了大量信息.
Formatting the values (converting from floating point to string) after extraction is the simplest way to implement things. You could even hide that inside some sort of wrapper so that the rest of the code doesn't have to deal with the consequences. But if you're going to do arithmetic on the value afterwards then you're better off not formatting and instead working with the value as returned by the query, because the format and reconvert back to binary floating point (which Python uses, just like the vast majority of other modern languages) loses lots of information in the reduced precision.
这篇关于SQLite的小数位问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！