如何使用mysql子查询减去库存和销售？

Trying to learn more on sub-query. I am looking for a way to subtract and compare two tables.

试图了解有关子查询的更多信息。我正在寻找一种方法来减去和比较两个表。

1. Inventory
2. Sales

My data records are as follows:

我的数据记录如下:

Inventory:

mysql> select store_id, product_id, sum(quantity) as inventory from inventories where store_id = 1 group by product_id;
+----------+------------+-----------+
| store_id | product_id | inventory |
+----------+------------+-----------+
|        1 |          8 |        24 |
|        1 |         10 |         4 |
|        1 |         14 |        24 |
+----------+------------+-----------+
3 rows in set (0.00 sec)

Sales

mysql> select store_id, product_id, sum(quantity) as sales from sales where store_id = 1 group by product_id;
+----------+------------+-------+
| store_id | product_id | sales |
+----------+------------+-------+
|        1 |          8 |    12 |
|        1 |         14 |     2 |
|        1 |          8 |     1 |
+----------+------------+-------+
2 rows in set (0.00 sec)

What is the proper sub-query to have the following result?

具有以下结果的正确子查询是什么?

+----------+------------+-----------+-------+-----------+
| store_id | product_id | inventory | sales | remaining |
+----------+------------+-----------+-------+-----------+
|        1 |          8 |        24 |    12 |        12 |
|        1 |         14 |        24 |     2 |        22 |
|        1 |          8 |        12 |     1 |        11 |
+----------+------------+-----------+-------+-----------+

7 个解决方案

#1

To achieve the desired output you need to calculate running totals of product sales. To get meaningful data, the data in sales table must be ordered chronologically. So you need at least one more field to sort data - it doesn't matter if it's a timestamp, or id field. Let's assume there is an id field in sales table. This is a query to get what you described:

要获得所需的输出,您需要计算产品销售的运行总数。要获得有意义的数据,必须按时间顺序排序销售表中的数据。因此,您至少需要一个字段来对数据进行排序 - 无论是时间戳还是id字段都无关紧要。我们假设销售表中有一个id字段。这是一个获得你所描述内容的查询:

SELECT 
    sales.id,
    sales.store_id,
    sales.product_id,
    inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) as inventory,
    sales.quantity as sales,
    inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) - sales.quantity as remaining
FROM
    sales
        INNER JOIN
    inventories ON inventories.store_id = sales.store_id
        AND inventories.product_id = sales.product_id
        LEFT JOIN
    sales AS sales_2 ON sales_2.store_id = sales.store_id
        AND sales_2.product_id = sales.product_id
        AND sales_2.id < sales.id
GROUP BY sales.id , sales.store_id , sales.product_id
ORDER BY sales.id

The second instance of sales table called sales_2 is used to calculate the sum of earlier sales (sales_2.id<sales.id)

sales_2的第二个sales表实例用于计算早期销售额的总和(sales_2.id )

You can exclude sales.id from the select clause, but you need to keep it in group by and order by.

您可以从select子句中排除sales.id,但您需要将其保留在group by和order by中。

#2

You can use results from your queries and join them to calculate the remaining quantity for each product

您可以使用查询中的结果并将其加入以计算每种产品的剩余数量

SELECT 
a.store_id,
a.product_id,
a.inventory,
b.sales,
a.inventory - b.sales AS remaining
FROM (
SELECT store_id, product_id, COALESCE(SUM(quantity),0) AS inventory 
FROM inventories WHERE store_id = 1 
GROUP BY product_id) a
LEFT JOIN (
SELECT store_id, product_id, COALESCE(SUM(quantity),0) AS sales
 FROM sales WHERE store_id = 1 
 GROUP BY product_id ) b USING(store_id, product_id)

#3

Try joining the two tables as :

尝试将两个表连接为:

SELECT I.store_id, I.product_id, I.inventory, S.sales, (I.inventory - S.sales) AS remaining
FROM Sales S INNER JOIN INVENTOR I
ON I.store_id = S.store_id
AND I.product_id = S.product_id

#4

I posted an answer and then re-read what you wanted. I realized that I had mis-read something and I see that you do want sales to be treated as individual transactions that are deducted in sequence i.e. "history". You are still going to need some kind of transaction id or transaction date to determine which order they are to be applied in. That was already noted in another answer too. Perhaps you want to rely on a MySQL row id or something. I don't know enough about MySQL to tell help you with that.

我发布了一个答案,然后重新阅读了你想要的内容。我意识到我读错了东西,我发现你确实希望将销售视为按顺序扣除的个别交易,即“历史”。您仍然需要某种交易ID或交易日期来确定它们应用于哪个订单。在另一个答案中也已经注明了这一点。也许你想依赖MySQL行id或者什么。我不太了解MySQL,告诉你这方面的帮助。

select
    i.store_id, i.product_id,
    i.inventory - s.previous_sales as inventory,
    s.quantity as sales,
    i.inventory - s.previous_sales - s.quantity as remaining
from
    inventories as i inner join
    (
    select store_id, product_id, quantity,
        (
        select sum(quantity)
        from sales as s2
        where
            s2.store_id = s.store_id and s2.product_id = s.product_id
            /* all sales for this store and product prior to this one */
            and s2.[sequencing column] < s.[sequencing column]
        ) as previous_sales
    from sales
    group by store_id, product_id
    ) as s
        on s.store_id = i.store_id and s.product_id = i.product_id
where
    i.store_id = 1

I don't know how you're going to handle changes in your inventory or how far back to look in sales that need to be deducted from inventory. This addresses the problem as you wrote it up.

我不知道您将如何处理库存中的更改或者需要从库存中扣除的销售回溯量。这会在您编写时解决问题。

#5

The sample data is quite limited, but I believe we can make these assumptions.

样本数据非常有限,但我相信我们可以做出这些假设。

1. You cannot sell an item unless it has been recorded as inventory

除非已将商品记录为库存,否则您无法销售该商品

2. You can have items of inventory you have not sold (yet)

您可以拥有尚未售出的库存物品(尚未)

If is not necessary to aggregate inventory then you could use a single "derived table", which is a type of subquery, like this:

如果没有必要聚合库存,那么您可以使用单个“派生表”,这是一种子查询,如下所示:

    SELECT
          I.store_id
        , I.product_id
        , COALESCE(I.inventory, 0) AS INVENTORY
        , COALESCE(S.sales, 0) AS SALES
        , COALESCE(I.inventory, 0) - COALESCE(S.sales, 0) AS REMAINING
    FROM Inventory I
    LEFT JOIN (
          SELECT
                store_id
              , product_id
              , SUM(sales) AS SALES
          FROM Sales
          WHERE store_id = 1
          GROUP BY
                product_id
    ) S ON I.store_id = S.store_id
                AND I.product_id = S.product_id
    WHERE I.store_id = 1
    ORDER BY
          I.store_id
        , I.product_id
    ;

If it is necessary to also aggregate inventory you could use 2 "derived tables" like this:

如果还需要聚合库存,您可以使用2“派生表”,如下所示:

SELECT
      I.store_id
    , I.product_id
    , COALESCE(I.inventory, 0) AS INVENTORY
    , COALESCE(S.sales, 0) AS SALES
    , COALESCE(I.inventory, 0) - COALESCE(S.sales, 0) AS REMAINING
FROM (
      SELECT
            store_id
          , product_id
          , SUM(inventory) AS INVENTORY
      FROM Inventory
      WHERE store_id = 1
      GROUP BY
            product_id
) I
LEFT JOIN (
      SELECT
            store_id
          , product_id
          , SUM(sales) AS SALES
      FROM Sales
      WHERE store_id = 1
      GROUP BY
            product_id
) S ON I.store_id = S.store_id
            AND I.product_id = S.product_id
ORDER BY
      I.store_id
    , I.product_id
;

Or you may use "correlated subqueries" within the select clause like this:

或者您可以在select子句中使用“相关子查询”,如下所示:

SELECT
      I.store_id
    , I.product_id
    , COALESCE((
            SELECT
                  SUM(sales)
            FROM sales S
            WHERE S.store_id = I.store_id
                  AND S.product_id = I.product_id
      ), 0) AS SALES
    , I.inventory - COALESCE((
            SELECT
                  SUM(sales)
            FROM sales S
            WHERE S.store_id = I.store_id
                  AND S.product_id = I.product_id
      ), 0) AS REMAINING
FROM Inventory AS I
WHERE I.store_id = 1
ORDER BY
      I.store_id
    , I.product_id
;

You have asked which is the "proper sub-query". I believe all the above are correct syntactically, but "proper" I don't fully understand. If you mean which will perform best I would suggest derived tables in preference to correlated subqueries, but to arrive at an answer that applies to all situations is near impossible.

你问过哪个是“正确的子查询”。我相信以上所有语法都是正确的,但“适当”我并不完全理解。如果你的意思是哪个会表现最好,我会建议派生表优先于相关子查询,但要得出适用于所有情况的答案几乎是不可能的。

To evaluate best performance requires better definition of the data, tables and indexes, then use of execution plans is strongly recommended. It may also be the case that NOT using any subqueries is the best approach (i.e. joins).

要评估最佳性能,需要更好地定义数据,表和索引,因此强烈建议使用执行计划。也可能是不使用任何子查询是最好的方法(即连接)。

see the queries above as a demonstration here: http://sqlfiddle.com/#!9/fa6b6/1

请参阅上面的查询作为演示:http://sqlfiddle.com/#!9 / fa6b6 / 1

#6

The ideal subquery will be the subquery which will use the keys of your table.

理想的子查询将是子查询,它将使用表的键。

For your inventory table, you should have a PRIMARY KEY on (store_id, product_id).

对于您的库存表,您应该有一个PRIMARY KEY(store_id,product_id)。

ALTER TABLE inventories ADD PRIMARY KEY (store_id, product_id).

If this is not intended to be unique (in which case, it is not really an inventory, but an "incoming item" table), you can set an INDEX for those two columns.

如果这不是唯一的(在这种情况下,它实际上不是库存,而是“传入项目”表),您可以为这两列设置INDEX。

ALTER TABLE inventories ADD INDEX (store_id, product_id).

You have to define the same index in the sales table

您必须在sales表中定义相同的索引

ALTER TABLE sales ADD INDEX (store_id, product_id).

After defining those keys, we can know set the perfect and long performance subquery.

在定义了这些键之后,我们就可以知道设置完美和长效的子查询。

1. First hypothesis : you have a primary key defined in inventories for (store_id, product_id)

第一个假设:您在库存中定义了主键(store_id,product_id)

sql:

 SELECT t1.store_id, t1.product_id, t1.quantity inventory,
       IFNULL(sum(t2.quantity), 0) sales,
       t1.quantity-IFNULL(sum(t2.quantity), 0) remaining
     FROM inventories t1
         LEFT JOIN sales t2 ON t1.store_id=t2.store_id
         AND t1.product_id=t2.product_id
      WHERE t1.store_id=1
      GROUP BY t1.store_id, t1.product_id;

2. Second hypothesis : just an index, things get more complicated

第二个假设:只是一个指数,事情变得更加复杂

You do the same thing except that instead of querying inventories table, you will replace it with the following subquery:

除了查询库存表之外,您将执行相同的操作,您将使用以下子查询替换它:

SELECT store_id, product_id, SUM(quantity) quantity
  FROM inventory
  GROUP BY store_id, product_id;

The result will be the following:

结果如下:

 SELECT t1.store_id, t1.product_id, t1.quantity inventory,
       IFNULL(sum(t2.quantity), 0) sales,
       t1.quantity-IFNULL(sum(t2.quantity), 0) remaining
     FROM 
        (SELECT store_id, product_id, SUM(quantity) quantity
           FROM inventory
           GROUP BY store_id, product_id) t1
       LEFT JOIN sales t2 ON t1.store_id=t2.store_id
       AND t1.product_id=t2.product_id
     WHERE t1.store_id=1
     GROUP BY t1.store_id, t1.product_id;

#7

Assuming you have a table of products and a table of stores, then join those together, to get the combination of products and stores. This way a row isn't dependent on a product still being on inventory on a store you are interested in, or the product to have yet been sold from a store you are interested in.

假设您有一个产品表和一个商店表,然后将它们加在一起,以获得产品和商店的组合。这样,行不依赖于您感兴趣的商店中仍在库存中的产品,或者尚未从您感兴趣的商店销售的产品。

Then LEFT OUTER JOIN sub queries to get the inventory and sales or each product / store.

然后LEFT OUTER JOIN子查询以获取库存和销售或每个产品/商店。

SELECT b.store_id, a.product_id, IFNULL(c.inventory, 0), IFNULL(d.sales, 0) , (IFNULL(c.inventory, 0) - IFNULL(d.sales, 0)) AS remaining
FROM products a
INNER JOIN stores b
ON b.store_id = 1
LEFT OUTER JOIN 
(
    SELECT store_id, product_id, SUM(quantity) AS inventory 
    FROM inventories 
    WHERE store_id = 1 
    GROUP BY store_id, product_id
) c
ON a.product_id = c.product_id
AND b.store_id = c.store_id 
LEFT OUTER JOIN 
(
    SELECT store_id, product_id, SUM(quantity) AS sales 
    FROM sales 
    WHERE store_id = 1 
    GROUP BY store_id, product_id
) d
ON a.product_id = d.product_id
AND b.store_id = d.store_id