mysql操作遇到的坑（第一版）

1、当我们要统计数据表数量时，如果遇到多表查询，会出现一个主表对应多个子表的维度，我们会用到group by，但是不要再用统计函数去操作数据，因为统计还是会统计原数据

案例

SELECT

sum(`order`.goods_amount) AS total_goods_amount,

sum(`order`.order_amount) AS total_order_amount,

sum(`order`.redbag_amount) AS total_redbag_amount,

sum(`order`.spread_amount) AS total_spread_amount,

sum(`order`.redrain_amount) AS total_redrain_amount

FROM

ds_order AS `order`

LEFT JOIN ds_refundreturn AS refundreturn ON `order`.order_id = refundreturn.order_id

WHERE

1 = 1

AND `order`.order_state = '40'

AND refundreturn.refund_state < '3'

AND refundreturn.is_revoke = '0'

GROUP BY

`order`.order_id

这样得到的数据，会把子表多个订单ID给统计上去，而分组并没有限制到

 

2、可以用数据库子查询，最后再加一个总查询

select sum(a.total_goods_marketprice) from (

SELECT

(

goods.goods_marketprice * ordergoods.goods_num

) AS total_goods_marketprice

FROM

ds_order AS `order`

LEFT JOIN ds_refundreturn AS refundreturn ON `order`.order_id = refundreturn.order_id

LEFT JOIN ds_ordergoods AS ordergoods ON `order`.order_id = ordergoods.order_id

LEFT JOIN ds_goods AS goods ON ordergoods.goods_id = goods.goods_id

WHERE

1 = 1

AND `order`.order_state = '40'

AND refundreturn.refund_state < '3'

AND refundreturn.is_revoke = '0'

GROUP BY

`order`.order_id

)a;

 

3、mysql group concat 长度是有限制的，如果数据太多，会截取

4、如果在不同电脑查询值，排序值得到的数据是相同的，有可能会显示不同顺序的数据

5、mysql看or与in的条件是一样的，但是数量大的时候，or会影响性能

 亲，后面还会陆续更新！

转载于:https://www.cnblogs.com/FLy-1992/p/11187057.html