大家好啊,我是春光trevor,这几天江浙沪一带一直在下雨,仿佛永远下不完一样,大家出门记得带雨伞。
这几天在做销售报表,需要从多个订单商品关系表中提取出相关联的字段,其中用到了很多关联查询(left join)和聚合函数(sum),再加上聚合函数搭配了group by使用,导致sql语句过于复杂,就发生了非常魔幻的事情:比如同一订单出现多次,相同的商品反复出现,订单实付金额大于应付金额等,把我直接整郁闷了。给你们看一部分我做报表用到的sql,长这样:
SELECT f1.month AS`month`, f1.branchName AS `branchName`, f1.count AS `spotOrderCount`, f1.itemCount AS `spotGoodsCount`, f1.payTotal AS `spotTotalMoney`, f2.count AS `bookingOrderCount`, f2.itemCount AS `bookingGoodsCount`, f2.payTotal AS `bookingTotalMoney`, (f1.payTotal + f2.payTotal) AS `totalMoney`FROM ( SELECT t0.createTime AS `month`, t0.salesBranchId AS `branchId`, t1.branchName AS `branchName`, count(t0.id) AS `count`, SUM(t0.totalQty) AS `itemCount`, SUM(t0.payTotal) AS `payTotal` FROM order t0 LEFT JOIN branch t1 ON t1.id = t0.salesBranchId WHERE t0.serviceBranchId = '12' GROUP BY t0.salesBranchId, DATE_FORMAT(t0.createTime, '%Y-%m') ) f1 LEFT JOIN ( SELECT t0.createTime AS `month`, t0.salesBranchId AS `branchId`, t1.branchName AS `branchName`, count(t0.id) AS `count`, SUM(t0.totalQty) AS `itemCount`, SUM(t0.payTotal) AS `payTotal` FROM order t0 LEFT JOIN branch t1 ON t1.id = t0.salesBranchId WHERE t0.serviceBranchId = '13' GROUP BY t0.salesBranchId, DATE_FORMAT(t0.createTime, '%Y-%m') ) f2 ON f1.branchId = f2.branchId <include refid="orderReportSearchCondition"/>GROUP BY f1.branchId, DATE_FORMAT(f1.month, '%Y-%m')
你们害怕了吗?反正我是怕了(好吧,我承认我没见过世面,这种程度的sql就吓到了)
其实本来这种需求应该我在业务层把所有数据搂出来,再把每部分数据拼起来,这样才是正规做法,将来字段有变动也好改一点。但做都做到这一步了,也只好硬着头皮上,等下个版本再说了。
这样大量采用 A left join B on A.id = B.a_id 的方式取出关联数据的方式,极易造成A表的行数据重复。因为left join是把左表的数据行全部取出来,右表如果有匹配的行就一起取出,如果在B表中有多个行满足on条件,将会一起被取出来,造成主表A的行数据重复。最终,在报表里就会看到多个重复的订单。
而订单实付金额大于应付金额的情况,则是由于,当一个支付单对应多个订单时,我用支付单表做左关联查询订单表,造成支付单表行数据重复,同时又用了SUM函数求支付单表的实付总金额,就出现了实付总金额大于应付金额这种魔幻的情况。
最终,还是通过加group by等骚操作恢复正常了,希望明天更新没事。
老希望工程了:D