mysql - 如何根据外键从两个表中检索两个 SUM() 值并将它们相减

Question

我有3张 table

products table purchase table sale table<br/>
+----+---------+ +----+-----------+-------+ +----+------------+--------+
| id | product | | id | product_id| pieces| | id | product_id | pieces |
+----+---------+ +----+-----------+-------+ +----+------------+--------+
| 1 | Tyre | | 1 | 3 | 5 | | 1 | 2 | 3 |
+----+---------+ +----+-----------+-------+ +----+------------+--------+
| 2 | Switch | | 2 | 1 | 3 | | 2 | 1 | 2 |
+----+---------+ +----+-----------+-------+ +----+------------+--------+
| 3 | Ring | | 3 | 2 | 6 | | 3 | 3 | 3 |
+----+---------+ +----+-----------+-------+ +----+------------+--------+ | 4 | Wheel | | 4 | 3 | 4 | | 4 | 2 | 1 |
+----+---------+ +----+-----------+-------+ +----+------------+--------+

1. 我要SUM()基于/GROUP BY product_id 的 purchase 表和 sale 表中的 pieces 列。前- SELECT products, SUM(pieces) FROM purchase GROUP BY product_id销售表的相同查询。

2. 我想减去 SUM()结果找出剩余的部分。前- SUM(purchase.pieces) - SUM(sale.pieces) AS balance

3. 最后，我需要产品表中的产品名称、采购表中相同产品的总件数，这表示产品已售出多少次，例如轮胎 - 5 件，销售表相同，以及之后的剩余件数从产品的总购买量中减去产品的总销售额。

我如何使用 PHP 执行此操作？和 PDO ？

Answer 1

您可以对 pieces 列使用 SUM() 并计算差值

SELECT p.id,
       p.product,
       IFNULL(total_purchase,0) total_purchase,
       IFNULL(total_sales,0) total_sales,
       IFNULL(total_purchase,0) - IFNULL(total_sales,0) balance
  FROM (SELECT pd.id,
               pd.product,
               SUM(pc.pieces) total_purchase
          FROM products pd
          LEFT JOIN purchase pc
            ON pd.id = pc.product_id
         GROUP BY pd.id, pd.product
       ) p
  JOIN (SELECT pd.id,
               pd.product,
               SUM(sl.pieces) total_sales
          FROM products pd
          LEFT JOIN sales sl
            ON pd.id = sl.product_id
         GROUP BY pd.id, pd.product
       ) s
    ON p.id = s.id
 ORDER BY p.id

Result

id  product total_purchase  total_sales balance
1   Tyre    3               2           1
2   Switch  6               4           2
3   Ring    9               3           6
4   Wheel   0               0           0