MySQL 加入重复问题？

Question

考虑以下 MySQL 表:

死亡率(包含哪些饲养者有死亡仔 pig )

raiserID  |  issuedDate  |  rotationNo |  numberDead
-----------------------------------------------------
   0052      2012-08-03         1             3

PIGLET_PO(包含饲养者购买仔 pig 的交易)

~筹集者可以在此处拥有多个记录

raiserID  |  deliveredDate  |  rotationNo  |  noOfDeliveredPigs  |  sellerID
------------------------------------------------------------------------------
   0052       2012-07-20          1                  10                1
   0052       2012-07-21          1                  15                1

SELLER_LIST

sellerID |  sellerName  
------------------------
    1       Solar Farm

预期结果:

sellerName  |  population  |  deceased
-----------------------------------------
Solar Farm         25            3     

• 饲养员是指照顾/饲养 pig 的人
• rotationNo 是一个唯一标识符，指的是融资者已经进行或正在进行的融资周期

到目前为止，我有以下查询，它给出了与预期类似的结果，唯一的问题是对于已故列，我得到的值为 6，而不是 3。我不确定，但我的猜测是它与有两条记录/行的 piglet_po 有关。我在这里缺少什么？

SELECT s.sellerName AS sellerName, SUM(p.noOfDeliveredPigs) AS population, SUM(numberDead) AS heads
FROM mortality m

JOIN piglet_po p
ON m.raiserid = p.raiserid
AND m.rotationNo = p.rotationNo

JOIN seller_list s
ON p.sellerID = s.sellerID

// This WHERE condition and the given string value that follow came from and is based on<br/>a select drop-down box and shouldnt be modified since this is how I display them in html
WHERE DATE_FORMAT(m.issuedDate, '%M %Y') = 'August 2012' 
GROUP BY sellerName
HAVING SUM(numberDead) != 0

Answer 1

这是因为表 piglet_po(p.rotationNo) 的连接中使用的列有重复值。相反，请尝试此操作，或者您也可以在 SELECT 语句中使用 DISTINCT 子句。

SELECT s.sellerName AS sellerName, SUM(p.noOfDeliveredPigs) AS population, 
       SUM(numberDead) AS heads
FROM mortality m
    JOIN (SELECT rotationNo, raiserid, sellerID, SUM(noOfDeliveredPigs) AS noOfDeliveredPigs
          FROM  piglet_po 
          GROUP BY rotationNo, raiserid, sellerID
         ) p
         ON m.raiserid = p.raiserid
            AND m.rotationNo = p.rotationNo
    JOIN seller_list s
         ON p.sellerID = s.sellerID
WHERE DATE_FORMAT(m.issuedDate, '%M %Y') = 'August 2012'
GROUP BY sellerName
HAVING SUM(numberDead) != 0;