我有这样一个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)的值被改变,它被递增。我试着添加了左/右/内连接。但结果是一样的。如何在不增加总数的情况下为该表添加连接?
发布于 2017-08-02 22:09:38
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尝试上面的查询。
在这里,我从WHERE子句改为ON条件子句进行了更改。
https://stackoverflow.com/questions/45462902
复制相似问题