mySQL格式数字输出，千位分隔符 [英] mySQL format number output , thousands separator

问题描述

我有一个mySQL查询，该查询输出带逗号的十进制字段.

I have a mySQL query which is outputting decimal fields with a comma.

SELECT Metals.Metal, FORMAT(Fixes.GBPam, 3) AS AM, FORMAT(Fixes.GBPpm, 3) AS PM,     
DATE_FORMAT(Fixes.DateTime, '%d-%m-%y') AS Date
FROM Fixes, Metals
WHERE Metals.Id = Fixes.Metals_Id

字段GBPam和GBPpm均为decimal(10,5)

Fields GBPam and GBPpm are both of type decimal(10,5)

现在我希望在我的SQL查询中将AM和PM列格式化为小数点后3位-正确

Now I want columns AM and PM to be formatted to 3 decimal places in my sql query - Correct

我希望将数千个值的格式设置为xxxx.xxx，而不是x，xxx.xxx-错误

I want values in the thousands to be formatted as xxxx.xxx and not x,xxx.xxx - Incorrect

mysql查询的示例输出:

Example output from mysql query:

Metal       AM          PM          Date
Gold        1,081.334   NULL    11-09-12
Silver      21.009      NULL    10-09-12
Platinum    995.650     NULL    11-09-12
Palladium   416.700     NULL    11-09-12

您能看到Gold AM的输出是1,081.334吗?如何获取输出1081.334?

Can you see that output for Gold AM is 1,081.334? How can I get it to output 1081.334?

这对我来说真是痛苦，因为我必须用PHP来消除逗号.我希望只使用mysql来正确格式化它.

This is a pain in the ass for me because I have to then muck about in PHP to remove the comma. I would prefer to just get mysql to format it correctly.

推荐答案

只需使用ROUND，这是一个数字函数. FORMAT是字符串函数

Just use ROUND, this is a numeric function. FORMAT is a string function

ROUND(Fixes.GBPam, 3)

这篇关于mySQL格式数字输出，千位分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！