MySQL中使用FORMAT格式化SUM结果后如何正确排序？

这问题我之前也踩过坑！核心原因特别直白：FORMAT()函数返回的是字符串类型，数据库对字符串排序是按字符的ASCII码顺序来的，根本不会识别成数值大小。比如'87,400.00'的第一个字符是'8'，ASCII码比'7,456,234.00'的'7'大，所以字符串排序时会把它排在前面，完全忽略后面的数值规模。

给你两个最实用的解决办法：

方法1：直接用原始聚合值排序 #

在ORDER BY里直接用未格式化的SUM()计算式，不要用SELECT里的格式化字段。这样排序逻辑基于真实数值，显示的却是格式化后的友好字符串，完美兼顾需求：

SELECT 
  x.user as user, 
  u.name, 
  -- 格式化显示结果
  FORMAT(SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01' AND '2018-04-30', x.tcv, 0)), 2) AS formatted_tcv
FROM 
  your_table x
JOIN 
  users u ON x.user = u.id
GROUP BY 
  x.user, u.name
-- 用原始SUM数值排序，而非格式化后的字符串
ORDER BY 
  SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01' AND '2018-04-30', x.tcv, 0)) DESC;

方法2：子查询先算聚合值，再格式化 #

如果你的查询逻辑比较复杂，重复写SUM()表达式太繁琐，可以用子查询先算出原始的聚合结果，外层再做格式化和排序，代码可读性更高：

SELECT 
  sub.user as user, 
  u.name, 
  FORMAT(sub.total_tcv, 2) AS formatted_tcv
FROM (
  -- 子查询计算未格式化的总tcv
  SELECT 
    x.user,
    SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01' AND '2018-04-30', x.tcv, 0)) AS total_tcv
  FROM your_table x
  GROUP BY x.user
) sub
JOIN users u ON sub.user = u.id
-- 直接用子查询里的数值字段排序
ORDER BY sub.total_tcv DESC;

不推荐的方法：字符串转数值排序 #

虽然可以把格式化后的字符串去掉逗号再转成数值排序（比如MySQL里用CAST(REPLACE(formatted_tcv, ',', '') AS DECIMAL(18,2))），但这种方式会额外增加字符串处理的性能开销，而且如果格式化后的字符串有异常字符（比如误加的符号），还会导致转换错误，所以尽量别用。

总之，核心原则就是：数值排序一定要基于数值类型的字段，别用格式化后的字符串来做排序依据。

内容的提问来源于stack exchange，提问作者twigg