MySQL从隔离子查询中减去 [英] MySQL subtract from isolated subquery
问题描述
我有一个包含库存的表
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.
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)
所以1056-54-1 = 1001
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
我还需要使用累积清单来获取总计,而不是"SUM",除了对运输中的那些求和,因为(那些未在运输中的)拥有大量记录,并且需要一些时间才能达到SUM.我可以用它来汇总运输中的记录,因为记录要少得多
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
推荐答案
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)
这篇关于MySQL从隔离子查询中减去的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!