MySQL - 千位分隔符

发布于 2024-11-18 16:31:05 字数 680 浏览 2 评论 0原文

我想知道是否有办法在 SQL 查询中获取千位分隔符？

请求如下所示：

select coalesce(user, 'TOTAL') as "user",
    concat(count(items), ' items') as "nb_items",
    concat(replace(sum(amount), '.', ','), ' €') as "total_in_euro",
    concat(
        round((sum(amount) / (select sum(amount) from my_db) * 100), 2),
        ' %'
    ) as "share"
from my_db
group by user
with rollup

目前，CSV 结果是：

user;nb_items;total_in_euro
usr_1;20 items;20000 €;10,01 %
usr_2;254 items;17852,12 €;8,12 %

我想要的是这样的结果：

user;nb_items;total_in_euro
usr_1;20 items;20 000 €;10,01 %
usr_2;254 items;17 852,12 €;8,12 %

有办法做到这一点吗？

原文

I would like to know if there is a way to get a thousand separator in an SQL Query ?

The request looks like this :

select coalesce(user, 'TOTAL') as "user",
    concat(count(items), ' items') as "nb_items",
    concat(replace(sum(amount), '.', ','), ' €') as "total_in_euro",
    concat(
        round((sum(amount) / (select sum(amount) from my_db) * 100), 2),
        ' %'
    ) as "share"
from my_db
group by user
with rollup

Currently, the CSV result is:

user;nb_items;total_in_euro
usr_1;20 items;20000 €;10,01 %
usr_2;254 items;17852,12 €;8,12 %

What I want is a result like that :

user;nb_items;total_in_euro
usr_1;20 items;20 000 €;10,01 %
usr_2;254 items;17 852,12 €;8,12 %

Is there a way to do so ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

兔姬 2024-11-25 16:31:05

我不知道如何用空格来实现，但标准（用点或逗号 [德国即]）分隔可以通过 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'

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().

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'

回复收藏 0 原文

~没有更多了~