mysql inventory数据库_mysql库存数据库中的库存数量视图(Stock Quantity view in mysql inventory database)...-CSDN博客

本文链接：https://blog.csdn.net/weixin_42364855/article/details/113313816

该博客介绍了如何通过子查询在SQL中对相关数据进行分组和求和操作。作者提供了一个示例，展示了如何根据ComponentID将Order_Line和Component_Used表中的数据关联并分别求和，以获取每个组件的净数量。

摘要由CSDN通过智能技术生成

If you just use SUM() function it will sum all the values of the table. If you want to sum only the related values, you have to first group the values by an id and then sum them. That's what the below sub query does.

Here is a model of Table1- Order_Line

Here is a model of Table2- Component_Used

I have filled only necessary details. You can see that there are multiple values of Order_LineQuantity for a given component.

And similarly multiple values exist for Component_UsedQuantity for a given component.

I have linked this using the ComponentID from Component Table.

SELECT ComponentID AS ComponentID,(ordertable.sumorder-componenttable.sumcomponent) as quantity

FROM Component,

(SELECT Order_LineComponentID as id,SUM(Order_LineQuantity) as sumorder FROM Order_Line GROUP BY Order_LineComponentID)ordertable,

(SELECT Component_UsedComponentID as id,SUM(Component_UsedQuantity) as sumcomponent FROM Component_Used GROUP BY Component_UsedComponentID)componenttable

WHERE

ComponentID=ordertable.id and ComponentID=componenttable.id

Now when you execute the query, it selects the required details after all the grouping operations.