MySQL从十进制(13.6)转换为货币 [英] MySQL from decimal(13.6) to currency

问题描述

我正试图从十进制(13.6)值转换为欧元的货币

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

我现在得到这个结果:

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

我想要的是这样的东西:

What I want is something like this one:

╔══════════════╦═════════╗
║   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.

这篇关于MySQL从十进制(13.6)转换为货币的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！