mysql - 使用联接表中的条件限制 SUM() 中包含的结果？

Question

我正在尝试列出我的产品表，但带有指向我的交货订单的链接，以提供某些状态下的库存总额；分配、调度等

有一个“状态”字段来定义交付当前的位置，例如； 1 = 已分配，2 = 已 dispatch ，3 = 已交付等

这就是我目前的情况

SELECT product.*, sum(quantity) AS allocated FROM product 
    LEFT JOIN delivery_product ON ( product.id = product_id ) 
    LEFT JOIN delivery ON ( delivery_id = delivery.id AND delivery.status = 1 ) 
GROUP BY code           
ORDER BY code

它包括来自 delivery_product 的所有数量匹配，而我只想包括那些 delivery order status = 1 的数量。我认为这是因为我在状态限制开始之前首先加入 delivery_product 表，但我还能怎么做这个？

如果你能找到更好的方法来完成我的任务，加分:)

-

product
id  code   stock
1   ABC    1000
2   DEF    2000

delivery
id  status   date   etc
1   1        etc    etc

delivery-product
id  delivery_id   product_id   quantity
1   1             1            500


results
product.id product.code product.stock allocated
1          ABC          1000          500
2          DEF          2000          0

Answer 1

我认为对您的查询进行小幅修改应该可行:

SELECT product.*
,   sum(case when delivery.status = 1 then quantity else 0 end) AS allocated
FROM product 
    LEFT JOIN delivery_product ON ( product.id = product_id ) 
    LEFT JOIN delivery ON ( delivery_id = delivery.id AND delivery.status = 1 ) 
GROUP BY code           
ORDER BY code

您可以通过将 delivery 连接从左外连接转换为内连接来避免条件，但是如果交付记录具有状态，则查询不会返回 quantity 为零的行产品缺少 1。