mysql - MySQL select语句中的SUM递增

我有这个SQL：

SELECT
SUM(er.total)
AS total
FROM
campaigns AS cmp
JOIN versions AS cv ON (cv.campaign_id = cmp.id)
JOIN creatives AS cr ON (cr.version_id = cv.id)
JOIN records AS er ON (er.creative_id = cr.id)
WHERE
cmp.business_id IN (-1,'39909') AND
cv.campaign_id IN (-1,65288) AND
er.type IN (5,6,1) AND
er.date BETWEEN '2017-06-11' AND '2017-06-11'
GROUP BY version_id, er.date

我需要在where子句中添加一个新条件，为此它需要添加新的联接，如下所示-

    SELECT
    SUM(er.total)
    AS total
    FROM
    campaigns AS cmp
    JOIN products as p ON (p.campaign_id = cmp.id)
    JOIN versions AS cv ON (cv.campaign_id = cmp.id)
    JOIN creatives AS cr ON (cr.version_id = cv.id)
    JOIN records AS er ON (er.creative_id = cr.id)
    WHERE
    cmp.business_id IN (-1,'39909') AND
    cv.campaign_id IN (-1,65288) AND
    er.type IN (5,6,1) AND
    er.date BETWEEN '2017-06-11' AND '2017-06-11' AND
    p.product_id IN(1,2)
    GROUP BY version_id, er.date

但是，这里SUM(er.total)的值被更改，并且被增加。我尝试添加左/右/内部联接。但是结果是一样的。如何在不增加总数的情况下为此表添加联接？

最佳答案

SELECT
    SUM(er.total)
    AS total
    FROM
    campaigns AS cmp
    JOIN products as p ON (p.campaign_id = cmp.id and p.product_id IN(1,2))
    JOIN versions AS cv ON (cv.campaign_id = cmp.id)
    JOIN creatives AS cr ON (cr.version_id = cv.id)
    JOIN records AS er ON (er.creative_id = cr.id)
    WHERE
    cmp.business_id IN (-1,'39909') AND
    cv.campaign_id IN (-1,65288) AND
    er.type IN (5,6,1) AND
    er.date BETWEEN '2017-06-11' AND '2017-06-11' AND

    GROUP BY version_id, er.date

尝试上面的查询。

在这里，我通过放置条件ON子句而不是WHERE子句进行了更改。

关于mysql - MySQL select语句中的SUM递增，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45462902/