MySQL从孤立子查询中减去

发布于 2024-12-17 05:26:27 字数 938 浏览 0 评论 0原文

我有一个包含库存的表

ID   |   Product ID   |  In_Transit  |   Quantity   | Cumulative Quantity
=====+================+==============+==============+====================
1    |      1         |     0        |     1000     |      1000
2    |      1         |     0        |        1     |      1001
3    |      1         |     1        |       54     |      1055
4    |      1         |     1        |        1     |      1056

,因此产品 id 1 的总库存为“1056”,我使用 SELECT MAX(ID) 子查询与该表连接来获取其累积数量,即 1056。

我想获取库存总计(减去所有在途金额)

所以 1056 - 54 - 1 = 1001

我如何在一个查询中得到这个,以便我得到

Product ID | Total Inventory | Inventory on Hand (Excluding in Transit |
===========+=================+=========================================    
1          |     1056        |           1001

另外我需要使用累积库存来获取总计与“SUM”相反,除了对传输中的记录进行求和,因为(那些不在传输中的)有大量记录并且需要很长时间才能进行 SUM。我可以用它来对传输中的记录进行求和,因为记录要少得多

I have a table containing inventory

ID   |   Product ID   |  In_Transit  |   Quantity   | Cumulative Quantity
=====+================+==============+==============+====================
1    |      1         |     0        |     1000     |      1000
2    |      1         |     0        |        1     |      1001
3    |      1         |     1        |       54     |      1055
4    |      1         |     1        |        1     |      1056

So the total inventory for product id 1 is '1056' I get this using a SELECT MAX(ID) subquery join with the table to get its cumulative quantity which is 1056.

I would like to get the Inventory total (subtracting all the amounts in transit)

So 1056 - 54 - 1 = 1001

How would I get this in one query so i get

Product ID | Total Inventory | Inventory on Hand (Excluding in Transit |
===========+=================+=========================================    
1          |     1056        |           1001

Also i need to use the cumulative inventory to get the total as opposed to 'SUM', except for summing those in transit because (those not in transit) have a large number of records and they take ages to SUM. I can use it to sum those in transit because there are far fewer records

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

池木 2024-12-24 05:26:27
SELECT 
    a.product_id
  , a.cumulative as total_inventory
  , a.cumulative - COALESCE(b.quantity,0) AS inventory_on_hand
FROM table1 a
JOIN 
    ( SELECT MAX(id) AS max_id
      FROM table1
      GROUP BY product_id
    ) m ON (m.max_id = a.id)
LEFT JOIN
    ( SELECT product_id, SUM(quantity) 
      FROM table1 
      WHERE in_transit = 1
      GROUP BY product_id
    ) b ON (a.product_id = b.product_id)
SELECT 
    a.product_id
  , a.cumulative as total_inventory
  , a.cumulative - COALESCE(b.quantity,0) AS inventory_on_hand
FROM table1 a
JOIN 
    ( SELECT MAX(id) AS max_id
      FROM table1
      GROUP BY product_id
    ) m ON (m.max_id = a.id)
LEFT JOIN
    ( SELECT product_id, SUM(quantity) 
      FROM table1 
      WHERE in_transit = 1
      GROUP BY product_id
    ) b ON (a.product_id = b.product_id)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文