mysql - 使用 SUM 和 COUNT 进行多表查询

Question

我正在努力编写一个(对我而言!)复杂的查询，该查询将计算商品销售的总件数，从两个单独的表中提取数据。我正在使用 MySQL。

第一个表是一个简单的“order_contents”表(在我的示例中为 sale_ord_contents)，其中包含 item_id 和销售数量。某些商品可能是组件，在这种情况下，它们的内容以及每个子组件的数量存储在另一个表 sale_ord_assemble_contents 中。在我的示例中，唯一的“组件”是水果篮。我已记下其中一个“水果篮”条目的销售数量为 (2)，这意味着其所有内容的数量都应乘以 2。

我的目标是根据两个表之间的累计销售数量找到 20 种最常见的商品。我最困扰的部分是如何处理将子组件的值乘以 sale_ord_contents 表中父组件的数量。

我已经编写了这个处理 sale_ord_contents 计算的查询，但我在加入 sale_ord_assemble_contents 表中的数据时遇到问题...

SELECT
   item_id, name, sku,
   SUM(quantity) AS purchases
FROM
   sale_ord_contents
GROUP BY
   item_id
ORDER BY purchases DESC
LIMIT 20

我在 PHP 中这样做会更好，还是在 MySQL 中可行？如果是这样，SQL 专家可以帮助我编写一个提供所需结果的查询吗？谢谢!

Answer 1

更新了我的答案，请重试以下操作:

SELECT
    IF (soac.item_id IS NULL, soc.item_id, soac.item_id) as item_id,
    IF (soac.name IS NULL, soc.name, soac.name) as name,
    SUM(soc.quantity * COALESCE(soac.quantity, 1)) AS total_sold
FROM
    sale_ord_contents soc
        LEFT OUTER JOIN sale_ord_assembly_contents soac
           ON soac.order_item_id = soc.id
GROUP BY
    IF (soac.quantity IS NULL, soc.item_id, soac.item_id),
    IF (soac.name IS NULL, soc.name, soac.name)
ORDER BY total_sold DESC LIMIT 20