对表中的值求和，然后将其减去具有相同id的另一个表中的值的总和

本文关键字：id 另一个 求和 然后 | 更新日期: 2023-09-27 18:16:16

我有两个表，inventory_tbl和withwal_tbl，都有product_id，数量和金额。我想将inventory_tbl中的所有数量相加，然后减去withwal_tbl中两个表中具有相同product_id的所有数量之和。意义;

inventory_tbl 

product_id | quantity  | amount
1          | 10        | 10000
2          | 20        | 20000
3          | 30        | 30000

withdrawal_tbl 

product_id | quantity  | amount
1          | 5         | 5000
2          | 10        | 10000
3          | 20        | 20000

Remaining_tbl 

product_id | quantity  | amount
1          | 5         | 5000
2          | 10        | 10000
3          | 10        | 10000

目前我有这个SQL语句但是它给了我错误的信息

"SELECT inventory_tbl.product_id As 'Product ID', SUM(inventory_tbl.quantity) - SUM(withdrawal_tbl.quantity), SUM(inventory_tbl.total) - SUM(withdrawal_tbl.total) 
FROM withdrawal_tbl, inventory_tbl GROUP BY inventory_tbl.product_id"

谢谢!

对表中的值求和，然后将其减去具有相同id的另一个表中的值的总和

你在做交叉乘积，而不是内部连接，这是问题。

把你的查询改成这样

"SELECT inventory_tbl.product_id As 'Product ID', SUM(inventory_tbl.quantity) - SUM(withdrawal_tbl.quantity), SUM(inventory_tbl.total) - SUM(withdrawal_tbl.total) 
FROM withdrawal_tbl inner join inventory_tbl on inventory_tbl.product_id = withdrawal_tbl.product_id  GROUP BY inventory_tbl.product_id"

为期望的输出使用内部连接，尝试如下:

"SELECT inventory_tbl.product_id As 'Product ID', SUM(inventory_tbl.quantity) - SUM(withdrawal_tbl.quantity), SUM(inventory_tbl.total) - SUM(withdrawal_tbl.total) 
FROM inventory_tbl INNER JOIN withdrawal_tbl on inventory_tbl.product_id =withdrawal_tbl.product_id group by inventory_tbl.product_id;"
相关文章：