发现差异在MySQL的两个表的行数Finding difference in row count of two tables in MySQL

- 此内容更新于:2014-12-30
主题:

原文:

I have two tables, one stores the products and quantity we have bought, the other stores the sells. The current stock is therefore the sum of all the quantity columns in the bought table minus the number of rows in the sells table. How can this be expressed in MySQL. Remember that there are many different products.

EDIT: To make it harder, I have another requirement. I have the bought table, the sold table, but I also have the products table. I want a list of all the products, and I want to know the quantity available of each product. The problem with the current answers is that they only return the products that we have sold or bought already. I want all the products.

解决方案:
原文:

Try this


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)

This is a better solution than a few of the other ones posted, which do not account for the fact that some products may not have any corresponding rows in the sales table. You want to make sure that such products also show up in the results.

NVL is an Oracle-specific function that returns the value of the first argument, unless it's null, in which case it returns the value of the second argument. There are equivalent functions in all commercial DBMSes -- you can use CASE in MySQL to the same effect.

Marius的回复:我认为你意味着inv_t。产品在第一行。

(原文:I think you meant inv_t.product on the first line.)

Marius的回复:此外,它不显示产品,还# 39;t被出售。

(原文:Also, it does not show a product which hasn't been sold yet.)

SquareCog的回复:谢谢你的,固定的。它显示产品还# 39;t被出售,这# 39;为什么变# 39;年代一个外部连接。我添加了一个nvl;()调用,清楚地表明,这是应该发生的。

(原文:Thanks for the catch, fixed. It does show a product which hasn't been sold, that's why it's an outer join. I added an nvl() call, to make clear that this is what should be happening.)

Marius的回复:同样的事情在MySQL IFNULL()

(原文:IFNULL() does the same thing in MySQL)

解决方案:
我建议让“库存”和“销售”表视图,以便他们可重复使用和最终的查询变得非常简单。显然,字段和表名称需要改变以匹配您的模式。
原文:

I suggest making the "inventory" and "sales" tables into views, so that they are re-usable and the final query becomes very simple. Obviously the field and table names will need to change to match your schema.

--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;
解决方案:
这一出售数量是0时同样适用。
原文:
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;

This one also works when quantity sold is 0.