mysql - SQL 2内部加入其他数据结果

Question

谁能解释一下。

SELECT results.`referentie`,products.`categorie`,products.`url`,products.`inkoopPrijs`,SUM(results.`stockverschil`) AS 'stockverschil'
    FROM `results` results
    INNER JOIN products
    ON products.`referentie` = results.`referentie` 
    WHERE results.`datum` BETWEEN '2017-01-11' AND '2017-01-12' AND results.`referentie`= '1'    

结果是 stockverschil 结果为 200。(正确的数量)

SELECT results.`referentie`,products.`categorie`,products.`url`,products.`inkoopPrijs`,sold.`winstPerProductPerDag`,SUM(results.`stockverschil`) AS 'stockverschil'
    FROM `results` results
    INNER JOIN products
    ON products.`referentie` = results.`referentie` 
    INNER JOIN sold
    ON sold.`referentie` = results.`referentie`
    WHERE results.`datum` BETWEEN '2017-01-11' AND '2017-01-12' AND results.`referentie`= '1'

结果是 stockverschil 结果为 4800。我是否对 INNER JOIN 做错了什么？

Answer 1

似乎是关于 sold 的问题，是一个 [product] 对许多 [sold] 的关系

尝试使用SUM(sold.winstPerProductPerDag)

的适当聚合函数

SELECT results.`referentie`,products.`categorie`,products.`url`,products.`inkoopPrijs`,s.`winstPerProductPerDag`,SUM(results.`stockverschil`) AS 'stockverschil'
FROM `results` results
INNER JOIN products
ON products.`referentie` = results.`referentie` 
INNER JOIN ( select SUM(sold.`winstPerProductPerDag`) as `winstPerProductPerDag`, sold.`referentie` from sold group by sold.`referentie`) s on s.`referentie` = results.`referentie`
WHERE results.`datum` BETWEEN '2017-01-11' AND '2017-01-12' AND results.`referentie`= '1'