SQLite格式编号始终为2位小数

I have some tables in a SQLite database that contains FLOAT column type, now i want to retrieve the value with a Query and format the float field always with 2 decimal places, so far i have written i query like this one :

我在SQLite数据库中有一些包含FLOAT列类型的表，现在我想用Query检索值并将float字段格式化为2位小数，到目前为止我已经编写了像这样的查询：

SELECT ROUND(floatField,2) AD field FROM table

this return a resultset like the the following :

这将返回如下结果集：

3.56
----
2.4
----
4.78
----
3

I want to format the result always with two decimal places, so :

我想格式化结果始终有两个小数位，所以：

3.56
----
2.40
----
4.78
----
3.00

Is it possible to format always with 2 decimal places directly from the SQL Query ? How can it be done in SQLite ?

是否可以直接从SQL查询格式化2位小数？如何在SQLite中完成？

Thanks.

谢谢。

3 个解决方案

#1

You can use printf as:

您可以使用printf作为：

SELECT printf("%.2f", floatField) AS field FROM table;

for example:

例如：

sqlite> SELECT printf("%.2f", floatField) AS field FROM mytable;
3.56
2.40
4.78
3.00
sqlite>

#2

Due to the way sqlite stores numbers internally,

由于sqlite在内部存储数字的方式，

You probably want something like this

你可能想要这样的东西

select case when substr(num,length(ROUND(floatField,2))-1,1) = "." then num || "0" else num end from table;

#3

You could try Select Str(12345.6789, 12, 3)

你可以试试Select Str（12345.6789,12,3）

displays: ' 12345.679' ( 3 spaces, 5 digits 12345, a decimal point, and three decimal digits (679). - it rounds if it has to truncate, (unless the integer part is too large for the total size, in which case asterisks are displayed instead.)

显示：'12345.679'（3个空格，5个数字12345，一个小数点和三个十进制数字（679）。 - 如果必须截断，它会舍入，（除非整数部分对于总大小太大，在这种情况下而是显示星号。）

for a Total of 12 characters, with 3 to the right of decimal point.

总共12个字符，小数点右边有3个字符。