SQL 中处理千分位

实际工作中经常需要对金额类的数值做格式转换，比如有千分位四舍五入保留两位小数，下面介绍下几种常见数据库中这种转化的处理逻辑。

MySQL

SELECT FORMAT(123456789.1234567, 4);
-- 123,456,789.1235

FORMAT() 有三个参数，第一个是要转换的数值，第二个参数是（四舍五入）保留的小数位数，第三个参数用于指定区域设置（将决定转化结果千位分隔符和小数点的格式，缺省为'en US'）。

PostgreSQL

select to_char(123456789.12345, 'FM999,999,999,999.9999');
-- 123,456,789.1235

select to_char(123456789.68, '999,999,999,999.9999');
-- 123,456,789.6800

to_char() 的第一个参数是要转换的数值。
to_char() 的第二个参数中的 FM 用于抑制前导的零或尾随的空白（注意对比上面的两个语句，第二个语句没有 FM，小数位部分以0作了填充补齐4位小数），否则结果可能是一个固定宽度的字符串；.9999表示的是小数部分的位数这里是（四舍五入）保留4位小数，改成.99就是保留两位小数；FM999,999,999,999定义的是千分位格式和最长支持的有效数值长度，将其中的,改成_，结果中的千分位将以_分隔。

Oracle

select to_char(123456789.12345, 'FM999,999,999,999.9999')  from dual;
-- 123,456,789.1235

Oracle 的语法同 PostgreSQL

SQL Server

select convert(VARCHAR, cast(123456789.12345 as money), 1);
-- 123,456,789.12

由于一些历史原因，SQL Server 的底层仍然是 Sybase 的那套逻辑，绝大部分的函数名和语法跟 Sybase 都是通用的，包括这里的千分位转换语法，也就是说把 Sybase 上正常运行的 SQL 语句放在 SQL Server 里也能运行。上述语句包含两个函数，内层的 cast(123456789.12345 as money) 用于将数值转换为 money 格式，外层的 conver() 函数是转化的关键：第一个参数指定转换的目标格式；第二个参数求值 cast(123456789.12345 as money) 的结果是要转换的来源值；第三个参数，用1来指定转换格式为逗号分隔的千分位，同时小数点保留两位小数。
如果你想保留4位小数或者1位小数，对不起不支持，只能自己想办法，下面是一个有千分位且保留4位小数的示例：

select substring(convert(varchar, cast(123456789.12345 as money), 1), 1,
                 charindex('.', convert(varchar, cast(123456789.12345 as money), 0)))
       +
       convert(varchar, round(convert(float, '0.' + substring(convert(varchar, 123456789.12345 * 1.0000), charindex('.', convert(varchar, 123456789.12345 * 1.0000)) + 1, 5)), 4));
-- * 1.0000 是为了兼容整数, 4 为要保留的小数位数

原理是截取利用 SQL Server 原生函数生成千分位，并截取千分位部分及小数点，再拼上小数部分。

发表评论