sql库存 - 想知道商店剩下多少件商品 [英] sql inventory - want to know how many items left in store
问题描述
大家好，
我的主要问题是我想知道商店里剩下多少件物品，如{table1.column - table2.column}
和我的问题不明确我会尝试说出我想要完成的事情
i创建了包含许多表格的视图，如果我设置laptop_quantity = 25
第一批客户需要5我想要数量自动等于 - 分配
ID |客户：项目| location | quantity_store |分配|
1 [johns] [Lapto] [ITdep] [20] [5]
2 [james] [Lapto] [ITdep] [16] [4]
3 [cilena] [电脑] ] [Accessdep] [16] [0]
所以这可以通过触发器或什么来完成？
任何答案都会如此赞赏
抱歉如果我的问题不清楚
从上面你需要有一个专栏具有您要与之汇总的项目。在您的示例中，您有[Customers：items]，其中包含客户名称和项目名称。您需要先将项目名称与客户分开。
其次，要获得数量的总计数并分配，您可以按查询运行组，例如
选择项目，sum（quantity_store） as qty，sum（assign） as out from stock group by item;
这将给你一个36和10个每个项目的库存数量
不幸的是你的问题仍然很不清楚但是在列级别，你可以运行像这样的查询
选择项目，sum（quantity_store）< span class =code-keyword> as qty，sum（assign） as out，（quantity_store - assign） as instock 来自 stock group by 项目;
这会返回quantity_store和assign之间的区别。希望它有所帮助。
首先非常感谢你们，尤其是Anele ......我知道我的问题不够明确，但我该怎么做才能做到，但经过大量的搜索，我发现问题有同样的问题，问题是如果你有桌子
库存 CurrentQuantity OpenedOrder
SKU1 300 50
SKU2 100 10
< blockquote class =quote>Quote：每个订单将单独处理。如何在处理完每个订单后让数据库自动更新库存平板？
即
如果订单中有2个SKU1已经处理完毕，库存表将会自动显示298
和建议答案是
CREATE PROC dbo.ProcessOrder
@ Item int ，
@ Quantity int
AS
BEGIN
- 此处更新订单表
INSERT INTO dbo.Orders（ItemID，Quantity）
VALUES （ @ ItemID ， @ Quantity ）
- 此处更新库存
更新 dbo.Inventory
SET CurrentQuantity = CurrentQuantity - 数量
WHERE ItemID = @ ItemID
结束
但我仍然无法理解这是如何工作的？我知道触发器可以向表插入值但存储生产者如何？
任何教程或想法将再次受到赞赏
hi everyone ,,
my main question is i want ti know how many items left in store like {table1.column - table2.column}
and if my question is not clear i will try say what i want accomplish
i have created view that consist of many table and if i set laptop_quantity=25
the first customers took 5 i want quantity automatically be equaled - assign
ID |Customers: items | location |quantity_store| assign|
1 [ johns ][ Lapto ] [ ITdep][ 20 ] [ 5 ]
2 [ james ][ Lapto ] [ ITdep ][ 16 ] [ 4 ]
3 [cilena ][computer] [Accessdep][ 16 ] [ 0 ]
so is this can be done by trigger or what?
any answer will be so appreciated
and sorry if my question is not clear
From the above you need to have a column that has the item that you want to aggregate with. In your example you have [Customers: items] which has the customer name and the item name together. You need to separate the item name from the customer first.
Secondly, to have a total count of the quantity and assigned you can then run a group by query, e.g.
select item, sum(quantity_store) as qty, sum(assign) as out from stock group by item;
this will give you a qty of 36 and out of 10 per item in stock
Unfortunately your question is still very unclear however at column level, you can run a query like this
select item, sum(quantity_store) as qty, sum(assign) as out, (quantity_store - assign) as instock from stock group by item;
this returns the difference between quantity_store and assign. Hope it helps somehow.
in first thanks a lot to you guys especially Anele...i knew that my question was not clear enough but what to do that all i could do,,, but after a lot of search i found question it have same issue and the question is if you have table
Inventory CurrentQuantity OpenedOrder SKU1 300 50 SKU2 100 10
Quote:Each order will be processed individually. How can I have the database automatically update the inventory tablet after each order is processed?
i.e
If the order has 2 SKU1 in it got processed, the the inventory table will automatically show 298
and the suggest answer is
CREATE PROC dbo.ProcessOrder @Item int, @Quantity int AS BEGIN --Update order table here INSERT INTO dbo.Orders(ItemID,Quantity) VALUES (@ItemID, @Quantity) --Update Inventory here UPDATE dbo.Inventory SET CurrentQuantity = CurrentQuantity - Quantity WHERE ItemID = @ItemID END
but i still cannot understand how does this works i? i know trigger can insert value to table but store producer how ?
any tutorial or idea will be so appreciated thanks again
这篇关于sql库存 - 想知道商店剩下多少件商品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
