海滨擎蟹

mysql SUM 函数使用问题和精度计算

先看一下函数语法:

SUM(DISTINCT expression)

在没有匹配行 SUM 会返回 NULL 而不是 0,这是我之前没有注意到的点。

为了让结果不会 NULL 而是 0,可以使用 COALESCE(SUM(money), 0) 或者 IFNULL(SUM(money), 0)

如果需要设置保留小数精度,可以使用 FORMAT(COALESCE(SUM(money), 0), 2)

除 sql 处理精度以外,也可以通过 php 函数处理精度计算:php 高精度计算问题

一般用法

示例:查询完成订单总收益(这里 status=3 表示完成)

select SUM(money) total from orders where status = 3;

与 group by 联合

SUM 属于聚合函数,可以与 group by 进行分组查询

示例:查询店铺订单总收益

select SUM(money) total from orders where status = 3 group by shop_id;

注意: where 子句需要放在 group by 的前面。

与 having 联合

如果需要对于 SUM 结果进行筛选,此时需要用到 having。

示例:查询店铺订单总收益 > 0

select SUM(money) total from orders where status = 3 group by shop_id having total > 0;

增加对 SUM 结果的排序和分页

SELECT SUM(money) total FROM orders 
WHERE status = 3 
GROUP BY shop_id 
HAVING total > 0
ORDER BY total DESC
LIMIT 0,10

SUM 与 limit 单独联动时,limit 约束返回行数是不可行的,最终只会返回一行。

如果需要对表记录约束去除行数,可以考虑使用子查询。

示例:前十订单金额的店铺总金额

SELECT shop_id, SUM(money) total FROM (
SELECT shop_id, money FROM orders 
WHERE status = 3 
ORDER BY money DESC
LIMIT 0, 10) rank
GROUP BY shop_id 
HAVING total > 0
ORDER BY total DESC
LIMIT 0,10

另外发现:FORMAT 函数可以在 having 中使用,而在 order by 子句中是不可行的,不会报错,但也不会按照 FORMAT 后的结果排序。即 order by FORMAT(COALESCE(SUM(money), 0), 2) DESC 是不会按照店铺订单总金额倒序排序的。

与 join 联合

示例:完结订单总收益

SELECT SUM(profits.money) total FROM orders 
LEFT JOIN profits ON orders.id = profits.order_id
WHERE orders.status = 3 
GROUP BY orders.shop_id 
HAVING total > 0
ORDER BY total DESC
LIMIT 0,10

注意:需要确保联查记录 1:1,如果出现 1:n 的情况,可能会造成数据重复统计的错误。

当前页面是本站的「Google AMP」版。查看和发表评论请点击:完整版 »