MySQL 从十进制(13.6)到货币

2022-01-15 00:00:00 mariadb mysql

我正在尝试将十进制 (13.6) 值转换为欧元

I'm trying to get from an decimal(13.6) value to currency in EURO's

我现在得到这个结果:

╔══════════════╦═════════╗
║   total      ║ Date    ║
╠══════════════╬═════════╣
║8887616.500000║ 2017    ║
╚══════════════╩═════════╝

我想要的是这样的:

╔══════════════╦═════════╗
║   total      ║ Date    ║
╠══════════════╬═════════╣
║€8,887.616.50 ║ 2017    ║
╚══════════════╩═════════╝

或者这个:

╔══════════════╦═════════╗
║   total      ║ Date    ║
╠══════════════╬═════════╣
║   €M8,9      ║ 2017    ║
╚══════════════╩═════════╝

我确实尝试过从十进制转换，但没有成功

I did try to convert from decimal but had no luck with that

SELECT  SUM(totalExcl) AS total, DATE_FORMAT(date_add, '%Y') AS 'Date'
FROM ex.ps_oxo_quotation
WHERE saleType IN ('IEW' , 'As', 'Pr')
AND date_add >= '2017-01-01 00:00:00'
GROUP BY 'Date'
ORDER BY 'Date' DESC

推荐答案

这会给你一个欧元格式的总和:

This will give you a sum formatted in Euro: 

SELECT CONCAT('€', FORMAT(SUM(totalExcl), 2, 'de_DE')) AS total

将显示:€8.890.905,86

另一个请求的替代方案:

The other requested alternative: 

SELECT CONCAT('€M', FORMAT((SUM(totalExcl)/1000000), 1, 'de_DE')) AS total

将显示:€M8,9

请注意，此示例将根据标准 (LOCALE de_DE) 显示总和，而不是按照您要求的确切格式显示混合点."和逗号，"以非标准方式.如果您确实必须以这种方式格式化总和，则可以通过一些字符串操作轻松解决此问题.

Note that this example will show the sum according to standards (LOCALE de_DE), and not with the exact format you have requested, that have mixed dots "." and commas "," in a non standard way. This could easily be fixed with some string manipulation if you really must format the sum that way.

相关文章