MySQL Join 正在将结果相乘

Question

我在将两个表连接在一起时遇到问题，它不断地增加我的值“qtyFulfillable”。

第一，当我运行此查询时，我得到了正确的结果:sumQF = 85

 SELECT fbai.id, 
        fbai.sku,
        CASE WHEN SUM(fbai.qtyFulfillable) > 0 THEN SUM(fbai.qtyFulfillable) ELSE 0 END AS sumQF
 FROM FBAInventory fbai
 WHERE fbai.fbaInventoryReport_id = 62010
 AND     fbai.sku = 'ELO-250030'
 GROUP BY fbai.id
 ORDER BY sumQF DESC

但是当我开始添加联接时，我现在得到:sumQF = 14110

 SELECT fbai.id, 
        fbai.sku,
        CASE WHEN SUM(fbai.qtyFulfillable) > 0 THEN SUM(fbai.qtyFulfillable) ELSE 0 END AS sumQF, 
        CASE WHEN   SUM(fbas.quantityShipped) > 0 THEN SUM(fbas.quantityShipped) ELSE 0 END as sumQS
 FROM FBAInventory fbai
 LEFT JOIN  FBAShipment fbas
 ON fbai.sku = fbas.sku
 WHERE fbai.fbaInventoryReport_id = 62010
 AND     fbai.sku = 'ELO-250030'
 GROUP BY fbai.id
 ORDER BY sumQF DESC

出于某种原因，sql 将“fbas”表(连接表)中的总行数设为 166 行，然后乘以85 x 166 = 14110。

我需要做什么才能连接到该表中，同时又不让它将我的结果乘以连接表中的总行数。谢谢!

Answer 1

您可以在单独的查询中对第二个数量进行求和，然后JOIN 将其加入。我也喜欢使用 GREATEST 而不是 CASE 语句.

    SELECT fbai.id,
           fbai.sku,
           GREATEST(SUM(fbai.qtyFulfillable),0) sumQF,
           COALESCE(fbas_sum.value,0) sumQS /* COALESCE in case no rows */
      FROM FBAInventory fbai
 LEFT JOIN (
        SELECT sku,
               GREATEST(SUM(quantityShipped),0) value
          FROM FBAShipment
      GROUP BY sku
           ) fbas_sum
        ON fbas_sum.sku=fbai.sku
     WHERE fbai.fbaInventoryReport_id=62010
       AND fbai.sku='ELO-250030'
  GROUP BY id, sku, sumQS /* Added other non-aggregates for sanity */
  ORDER BY sumQF DESC

您确定要按 id 进行分组吗？我假设 id 是表的主键，因此应该只有一行要求和。