MYSQL where on sum(table1.column_name) < table2.other 列

Question

我有两个表 products 和 sell_products

products -> id|name|quantity|price
sold_products -> id|product_id|quantity

我需要的是计算 sell_products 中的数量总和，并检查总和是否大于产品表中的数量。如下所示；

select p.* from products as p and sold_products as sp where p.quantity > sum(sp.quantity)

提前致谢；

Answer 1

您可以通过join 2个表并使用having和group by来实现此目的:

select p.quantity, sum(sp.quantity) 
from products as p 
inner join sold_products as sp
on p.id = sp.product_id
group by p.id
having p.quantity < sum(sp.quantity)