MYSQL-千位分隔符 [英] MYSQL - Thousands separator
问题描述
我想知道是否有一种方法可以在SQL查询中获得千位分隔符吗?
I would like to know if there is a way to get a thousand separator in a SQL Query ?
由于我有点懒,所以我想建立一个请求,使我可以在不添加此分隔符的情况下将结果复制/粘贴到老板,而不用添加:D
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
请求看起来像这样:
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 %
有办法吗?
谢谢
B
推荐答案
我不知道如何使用空格,但是可以使用以下方法实现标准的分隔(使用点或逗号[德语即]): FORMAT()
.
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()
.
看看格式化函数
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'
这篇关于MYSQL-千位分隔符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!