MYSQL - 千位分隔符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6611362/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL - Thousands separator
提问by Nugget
I would like to know if there is a way to get a thousand separator in a SQL Query ?
我想知道是否有办法在 SQL 查询中获得千位分隔符？
As i'm a bit lazy, I want to build a request which can allow me to copy/paste the result to my boss without adding this separator :D
由于我有点懒，我想构建一个请求，它可以让我将结果复制/粘贴给我的老板，而无需添加此分隔符：D
The request looks like this :
请求如下所示：
SELECT COALESCE(Customer, 'TOTAL') AS "Customer", CONCAT( COUNT( SegmentId ) , ' bookings' ) AS "Nb bookings", CONCAT( REPLACE( SUM( Price ) , '.', ',' ) , ' ' ) AS "Total ()", CONCAT( ROUND( (
SUM( Price ) / (
SELECT SUM( Price )
FROM my_db
WHERE CreationDate = CURRENT_DATE( )
AND SegmentStatus = "OK" ) *100 ) , 2 ) , ' %'
) AS "PDM"
FROM my_db
WHERE CreationDate = CURRENT_DATE( )
AND SegmentStatus = "OK"
GROUP BY Customer
WITH ROLLUP
Currently, the result is (table with ';' separator, sorry I didn't manage to make a table with this editor :( ) :
目前，结果是（带有 ';' 分隔符的表格，抱歉我没有设法用这个编辑器制作表格 :( ) ：
Customer;Nb bookings;Total ();PDM
cust_1;20 bookings;20000 ;10,01 %
cust_2;254 bookings;17852,12 ;8,12 %
What I want is a result like that :
我想要的是这样的结果：
Customer;Nb bookings;Total ();PDM
cust_1;20 bookings;20 000 ;10,01 %
cust_2;254 bookings;17 852,12 ;8,12 %
Is there a way to do so ?
有没有办法这样做？
Thank,
谢谢，
B
乙
回答by Jacob
I don't know how to do it with a space, but the standard (with a point or a comma [germany i.e.]) seperation can be achieved with FORMAT().
我不知道如何用空格来做，但标准（用点或逗号 [德国 ie]）分隔可以用FORMAT().
Have a look at the Format Function
看看格式函数
mysql> SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
-> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
-> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
-> '12.332,20'
mysql> SELECT FORMAT(12332.2,2,'pt_BR');
-> '12332,20'
