发现差异在MySQL的两个表的行数Finding difference in row count of two tables in MySQL
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.
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.
(原文：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;
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.
- 当我应该使用执照;不是NULL”在MySQL表和有什么好处吗?When should I be using “NOT NULL” in a MySQL table and are there any benefits?
- 你怎么两个语句吗?How do you OR two LIKE statements?
- 个人项目——下一个实际的语言/技术学习Personal Project - Next practical language/tech to learn
- MySQL—;马克1匹配的行MySQL — mark all but 1 matching row
- 什么你读过的最好的书或文章优化mysql服务器(linux)?(关闭)Whats the best book or article you have read on optimizing mysql servers (linux)? [closed]
- 发现差异在MySQL的两个表的行数Finding difference in row count of two tables in MySQL
- 如何设置连接超时根据MySQL用户登录的吗How to setup a connection timeout depending of the user login in MySQL
- MySQL计数(不同的())意想不到的结果MySQL COUNT(DISTINCT()) unexpected results
- 有可能有一个MySQL索引视图?Is it possible to have an indexed view in MySQL?
- 什么是适当的交叉表的SQL查询语法吗?What is the proper syntax for a cross-table SQL query?
- 在MySQL中变量限制条款Variable LIMIT Clause in MySQL