在MySQL中查找两个表的行数差异

试试这个

SELECT inv_t.product_id, inventory_total-nvl(sales_total,0)
FROM 
  (SELECT product_id, sum(quantity) as inventory_total
   FROM inventory
   GROUP BY product_id) inv_t LEFT OUTER JOIN
  (SELECT product_id, count(*) AS sales_total 
   FROM sales 
   GROUP BY product_id) sale_t
  ON (inv_t.product_id = sale_t.product_id)

这是一个比其他一些已发布的解决方案更好的解决方案,这些解决方案不能说明某些产品在Sales表中可能没有任何对应的行。您要确保这些产品也出现在结果中。

nvl是一个特定于Oracle的函数,它返回第一个参数的值,除非它为空,在这种情况下,它返回第二个参数的值。在所有商业DBMS中都有等价的函数——您可以在MySQL中使用case以达到相同的效果。

我建议将“inventory”和“sales”表制作成视图,这样它们就可以重用,并且最终的查询变得非常简单。显然,字段和表名需要更改以匹配您的模式。

--First view: list products and the purchased qty
create or replace view product_purchases as
select
  product_id
 ,sum(purchased_qty) as purchased_qty
from
  purchases
group by
  product_id;

--Second view: list of products and the amount sold    
create or replace view product_sales as
select
  product_id
 ,count(*) as sales_qty
from
  sales
group by
  product_id;

--after creating those two views, run this query:
select
  pp.product_id
 ,pp.purchased_qty - ps.sales_qty as on_hand_qty
from
  product_purchases pp
 ,product_sales ps
where ps.product_id = pp.product_id;

SELECT product AS prd, 
SUM(quantity) - 
  IFNULL((SELECT COUNT(*)
   FROM sells
   WHERE product = prd 
   GROUP BY product), 0)
AS stock 
FROM bought
GROUP BY product;

当售出数量为0时,此选项也有效。