mysql - 同一张表的多个联接计数错误

我有2张桌子

1-优惠券

2-牵引力

对于每个优惠券，牵引力表中可能会有几行
我想获得所有优惠券的清单以及在不同条件下的牵引力计数

    SELECT `coupons`.`id` ,
            count( tractions_all.id ) AS `all` ,
            count( tractions_void.id ) AS void,
            count( tractions_returny.id ) AS returny,
            count( tractions_burned.id ) AS burned
      FROM `coupons`
               LEFT JOIN `tractions` AS `tractions_all`
                         ON `coupons`.`id` = `tractions_all`.`coupon_parent`
               LEFT JOIN `tractions` AS `tractions_void`
                         ON `coupons`.`id` = `tractions_void`.`coupon_parent`
                         AND `tractions_void`.`expired` =1
               LEFT JOIN `tractions` `tractions_returny`
                         ON `tractions_returny`.`coupon_parent` = `coupons`.`id`
                         AND `tractions_returny`.`expired` =11
               LEFT JOIN `tractions` `tractions_burned`
                         ON `tractions_burned`.`coupon_parent` = `coupons`.`id`
                         AND `tractions_burned`.`expired` =0
                         AND '2014-02-12'
     WHERE `coupons`.`parent` =0
  GROUP BY `coupons`.`id`

现在，我的一张优惠券都只有2牵引力，而burned traction其他的优惠券都没有牵引力

这是结果



如您所见，带有id=13的优惠券的牵引力为4，而应该为2 ...我在做什么错？如果我删除最后一个连接，它工作正常，我得到2

最佳答案

您一次沿多个维度进行汇总，因此每个ID都会产生笛卡尔积。

如果您的数据量不是很大，则解决此问题的最简单方法是使用distinct：

SELECT `coupons`.`id` ,
       count(distinct tractions_all.id ) AS `all` ,
       count(distinct tractions_void.id ) AS void,
       count(distinct tractions_returny.id ) AS returny,
       count(distinct tractions_burned.id ) AS burned

如果数据很大，则可能需要先将值聚合为子查询，然后再进行联接。

关于mysql - 同一张表的多个联接计数错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21730123/