How to use SQL to query the following data structure of the goods remaining inventory

Problem Description: Find out the remaining stock of all items in the store according to the store ID?

Business Description: The item's remaining stock = The total inventory record of the goods and-the total sales of the goods and.

The database table structure after abstraction is as follows:

Question abstraction: Use MySQL to check the remaining inventory information for all items in store 1th?

Thank you: hope that the great God to help solve, the SQL is too bad, if a SQL can not be implemented, it is possible to say SQL with PHP implementation of the method, of course, if SQL can achieve the best. Thank you so much!

Reply content:

Problem Description: Find out the remaining stock of all items in the store according to the store ID?

Business Description: The item's remaining stock = The total inventory record of the goods and-the total sales of the goods and.

The database table structure after abstraction is as follows:

Question abstraction: Use MySQL to check the remaining inventory information for all items in store 1th?

Thank you: hope that the great God to help solve, the SQL is too bad, if a SQL can not be implemented, it is possible to say SQL with PHP implementation of the method, of course, if SQL can achieve the best. Thank you so much!

It seems that no one answered Ah, in fact, this is the database design when the idea of it. If the inbound record is combined with the sales record in a table and then solved with a single SQL, the merged table structure

Store ID	Product ID	Warehousing	Sales
001	1	10	3
001	2	10	5

SQL as follows

select productid, sum(stock)-sum(sale) as total from `record` where shopid = '001' GROUP BY productid;

[personal view] Such a design mechanism and the upper phase is not much different from the structure, the design is also basically reasonable, from the calculation, now this structure should be faster.

give you a direction! First use GROUP by the past a single product sales total, and then use the table associated with the way, the amount of incoming and sales to calculate the amount of

Design problems, very typical Pat head design, no consideration of demand analysis

This is the SQL statement, multi-table query, a SQL implementation, select shop.*,product.*,stock.*,sale.*,stock.count - sale.count as ss from shop left join product on shop.id = product.shop_id left join stock on product.id = stock.productid left join sale on product.id = sale.product_id where stock.count > 0 and sale.count > 0 and the final result has given the remaining inventory of each commodity,

Obviously, I think the data table design is unreasonable, since the surplus inventory is so important, I think I need to add this field.
Multi-Table association queries are slow and CPU-intensive, and the rest of the inventory needs to be read in a lot of times, and every time this query gets obvious I think it's unreasonable.

