mysql - 左连接和 GROUP BY 的 SUM 不正确

Question

我得到的总存款总额结果有误。我想输出每个campaign_name的总存款报告最终在日期范围内。

 SELECT IFNULL(campaign_name,'DIRECT'),
       IFNULL(TotalDeposit,0)
FROM trackings
LEFT JOIN
  (SELECT deposit_amount,
          sum(deposit_amount) AS TotalDeposit,
          uuid
   FROM conversions
   LEFT JOIN transactions ON conversions.trader_id = transactions.trader_id
   WHERE aff_id =3
     AND TYPE='deposit'
   GROUP BY transactions.trader_id) AS conversions ON trackings.uuid = conversions.uuid
WHERE aff_id=3
GROUP BY campaign_name

结果:trynow 事件中缺少 200 个？

campaign_name,TotalDeposit
DIRECT,0.00
new_campaign_name,0.00
test march,500.00
testing,0.00
trynow,800.00

预期结果:

campaign_name,TotalDeposit    
DIRECT,0.00
new_campaign_name,0.00
test march,500.00
testing,0.00
trynow,1000.00

Answer 1

我认为你的数据不太正确 - 使用你提供的数据，测试游行的 500 押金永远不会被退回，因为它链接到 trader_id 7506，他在交易中没有记录转换表。

但是，下面的查询更简单，更容易理解，并且为 trynow 正确返回 1000

SELECT
      IFNULL(SUM(t.deposit_amount),0) AS total_deposits
    , IFNULL(tr.campaign_name,'DIRECT') AS campaign


FROM
    trackings tr            LEFT JOIN

    conversions c ON
        tr.uuid = c.uuid    LEFT JOIN

    transactions t ON
        c.trader_id = t.trader_id AND
        tr.`aff_id` = t.aff_id AND
        t.type = 'Deposit' 

WHERE
    tr.aff_id = 3  AND
    tr.updated_at >= '2015-03-01' AND tr.updated_at < '2015-04-01'

GROUP BY
    IFNULL(tr.campaign_name,'DIRECT')

如果您可以检查提供的测试数据或以其他方式为我指明正确的方向，我也许能够改进查询以准确返回您想要的内容。

有关日期过滤，请参阅上面的 where 子句的附加内容。注意，如果您需要过滤 transactions 表中的日期，则日期过滤子句必须是“on”语句的一部分(因为该表是左连接的，所以我们无法在主 where 子句中进行过滤) .