mysql如何在数字格式之后对结果排序？

wvmv3b1j 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(368)

我想返回按tcv和的结果降序排列的结果。如果我删除format（）函数，这个方法就可以了，但是当我将format（）函数添加到sum中时，它会按第一个数字的顺序返回结果，而不是查看整个数字。
因此它将返回7456234.00之前的87400.00，因为8在7之后，这显然不是期望的结果。

SELECT
x.user as user, u.name, 
FORMAT(SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01 00:00:00' AND '2018-04-30', x.amount_base, 0)), 2) as tcv, 
FORMAT(SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01 00:00:00' AND '2018-04-30', x.mrr_base, 0)), 2) as mrr,
FROM table AS x 
LEFT JOIN users u ON x.owner = u.name WHERE x.resign_new = 'New' AND x.owner IN ("Name 1", "Name 2", "Name 3")  
GROUP BY x.owner   
ORDER BY tcv DESC

我需要在地方的数字格式需要与可读性逗号分隔符格式。
mysql不正确地返回订单，我做错了什么？

sql mysql sql-order-by

来源：https://stackoverflow.com/questions/49983340/mysql-how-to-order-results-after-a-number-format

1条答案

按热度按时间

pxy2qtax1#

sum函数根据要聚合的列的数据类型返回十进制或双精度。因此，要正确排序，可以删除orderby中的format子句。见下文。

SELECT
x.user as user, u.name, 
FORMAT(SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01 00:00:00' AND '2018-04-30', x.amount_base, 0)), 2) as tcv, 
FORMAT(SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01 00:00:00' AND '2018-04-30', x.mrr_base, 0)), 2) as mrr,
FROM table AS x 
LEFT JOIN users u ON x.owner = u.name WHERE x.resign_new = 'New' AND x.owner IN ("Name 1", "Name 2", "Name 3")  
GROUP BY x.owner   
ORDER BY (SUM(IF(x.stage IN ('Closed Won') AND x.close_date BETWEEN '2018-04-01 00:00:00' AND '2018-04-30', x.amount_base, 0))) DESC

赞(0）回复(0）举报 2021-06-21

我来回答

mysql如何在数字格式之后对结果排序？

1条答案

相关问题

热门标签

最新问答