SQLITE - 计算剩余物品数量 [英] SQLITE - calculate remaining item quantities

问题描述

我有一个库存表，其中包含特定商品 ID/颜色的商品总数:

I have a stock table that holds the number of total items for a specific item id/colour:

stock_table

    item_code    |    colour    |    stock
---------------------------------------------
       A         |    red       |    10
       A         |    blue      |    8
       A         |    yellow    |    5
       B         |    red       |    5
       B         |    blue      |    0
       B         |    yellow    |    15

然后我有一个表，其中存储了已订购的商品数量(请注意，可以在不同的订单中找到相同的商品)

then I have a table where already ordered items quantities are stored (note that the same item can be found in different orders)

orders_table

    order_id     |    item_code    |    colour    |    ordered_qty
----------------------------------------------------------------------
       1         |        A        |     red      |        3
       1         |        A        |     blue     |        1
       1         |        A        |     yellow   |        2
       1         |        B        |     red      |        1
       2         |        A        |     red      |        1
       2         |        A        |     yellow   |        3
       2         |        B        |     yellow   |        15

我需要创建一个表来保存可用的剩余库存数量:例如，对于库存表中每件商品的数量，我需要从订单表中减去已订购"数量，以获得如下结果:

I need to create a table that holds the remaining stock quantities available: for example for each item's qty in the stock table, I need to subtract the "already ordered" qty from orders table, to get something like this:

remaining_table

    item_code    |    colour    |    remaining
------------------------------------------------
       A         |    red       |    6
       A         |    blue      |    7
       A         |    yellow    |    0
       B         |    red       |    4
       B         |    blue      |    0
       B         |    yellow    |    0

我正在考虑使用视图，因为表格会为每个新插入的订单行自动更新.我可以获得每种商品/颜色的已订购"数量:

I was thinking of using a view, as the table will auto update for every new inserted order row. I'm able to get the "already ordered" quantities for each item/colour:

SELECT item_code, colour, SUM(stock) as already_placed FROM orders_table GROUP BY item_code, colour

此查询将返回类似的内容(请注意，从未订购过 B/blue):

this query will return something like (note that B/blue has never been ordered):

    item_code    |    colour    |    already_placed
----------------------------------------------------
       A         |    red       |    4
       A         |    blue      |    1
       A         |    yellow    |    5
       B         |    red       |    1
       B         |    yellow    |    15

现在我坚持构建查询，该查询将在表或其他视图中为我提供剩余表数量..

Now I'm stuck on building the query that will give me, in a table or in another view, the remaining_table quantities..

长话短说，我需要从原始stock_table 中减去already_placed 数量，你能帮我吗?我在这里找不到任何减"或减"函数http://www.sqlite.org/lang_aggfunc.html

long story short, I need to subtract from the original stock_table the already_placed quantities, can you please help me? I can't find any "minus" or "subtract" function here http://www.sqlite.org/lang_aggfunc.html

可以吗?

提前致谢，最好的问候

推荐答案

您可以使用相关子查询来计算订购了多少特定商品:

You can use a correlated subquery to compute how many specific items have been ordered:

CREATE VIEW remaining_view AS
SELECT item_code,
       colour,
       stock - (SELECT IFNULL(SUM(ordered_qty), 0)
                FROM orders_table
                WHERE orders_table.item_code = stock_table.item_code
                  AND orders_table.colour    = stock_table.colour)
       AS remaining
FROM stock_table

SQL 小提琴

这篇关于SQLITE - 计算剩余物品数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！