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

Problem description: How can I find the remaining inventory of all products in the store based on the store ID? Business Description: remaining inventory of the product the total inventory records of the product andProblem description: How can I find the remaining inventory of all products in the store based on the store ID? Business Description: remaining inventory of the Product = total inventory records of the product and-total sales and.

The abstract database table structure is as follows:

Question Abstract: How can I use MySQL to query the remaining inventory information of all products in Shop 1?

Thanks: I hope you can help me solve this problem. It is really bad to learn SQL. If an SQL statement cannot be implemented, let's talk about the method of combining SQL with PHP. Of course, if SQL can be implemented best. Thank you very much!

Reply content:Problem description: How can I find the remaining inventory of all products in the store based on the store ID? Business Description: remaining inventory of the Product = total inventory records of the product and-total sales and.

The abstract database table structure is as follows:

Question Abstract: How can I use MySQL to query the remaining inventory information of all products in Shop 1?

Thanks: I hope you can help me solve this problem. It is really bad to learn SQL. If an SQL statement cannot be implemented, let's talk about the method of combining SQL with PHP. Of course, if SQL can be implemented best. Thank you very much!

It seems that no one answered the question. In fact, this is also the idea of database design. If the warehouse receiving record is combined with the sales record in a table, you can use an SQL statement to solve the problem. The merged table structure

Store ID	Item ID	Warehouse receiving	Sales
001	1	10	3
001	2	10	5

The SQL statement is as follows:

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

[Personal opinion] the structure of such a design mechanism is not much different from the above, and the design is also basically reasonable. In terms of computing, the structure should be faster now.

Give you a direction! Use group by to calculate the total sales volume of a single item in the past, and then use the table association method to calculate the number of incoming databases and total sales volume.

Design Issues, typical head shoot design, without considering Demand Analysis

This is an SQL statement, multi-Table query, one 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 > 0The final result shows the remaining inventory of each item,

Obviously, I think the data table design is unreasonable. Since the remaining stock is so important, I think this field needs to be added.
The speed of Multi-table join queries is relatively slow, and it consumes a lot of CPU, while the remaining inventory needs to be read in many cases. It is obvious that I think it is unreasonable to query each time.