mysql - Subtract two columns from two tables with Group By
问题描述
I have 4 tables.
CREATE TABLE Branch(
ID INT,
Name VARCHAR(50)
);
INSERT INTO Branch VALUES
(1,'A'), (2,'B');
CREATE TABLE Product(
ID INT,
Name VARCHAR(50)
);
INSERT INTO Product VALUES
(1,'X'), (2,'Y');
CREATE TABLE StockIn(
ID INT,
ProductId INT,
Quantity INT,
BranchId INT
);
INSERT INTO StockIn VALUES
(1,1,10,1),
(2,1,20,1),
(3,1,50,2),
(4,1,10,2);
CREATE TABLE StockOut(
ID INT,
ProductId INT,
Quantity INT,
BranchId INT
);
INSERT INTO StockOut VALUES
(1,1,5,1),
(2,1,21,1),
(3,1,45,2),
(4,1,5,2);
Now i want to calculate stock from these (StockIn-StockOut).
by using below query i got stockin and stockout from stock tables by grouping thier branches.
StockIn
select BranchId, ifnull(sum(Quantity),0) Quantity from stockin where productid=1 group by BranchId;
StockOut
select BranchId, ifnull(sum(Quantity),0) Quantity from stockout where productid=1 group by BranchId;
and i want to show the result like this
解决方案
- 对于每个单独的 Select 查询结果,获取一个附加字段,即factor。进货时其值为+1,缺货时为-1。
- 使用Union All组合单个选择查询的结果,并将结果集用作派生表。
- 现在,只需在 BranchId 的分组上再次进行求和,乘以因子。
尝试以下查询:
SELECT derived_t.BranchId,
SUM(derived_t.factor * derived_t.quantity) AS Quantity
FROM
(
select BranchId,
ifnull(sum(Quantity),0) as quantity,
1 as factor
from stockin
where productid=1
group by BranchId
UNION ALL
select BranchId,
ifnull(sum(Quantity),0) Quantity,
-1 as factor
from stockout
where productid=1
group by BranchId
) AS derived_t
GROUP BY derived_t.BranchId
推荐阅读
- asp.net-core - ASP.NET Core 项目中的会话变量值问题。在页面第二次加载时获取值
- python - 如何在 tkinter 中为小部件设置固定宽度和高度?
- node.js - DigitalOcean Spaces / Amazon S3“InvalidArgument:Request.extractError 处为空”
- django - 如何在后端实现推送通知的通知队列?
- r - R - 替换给定数据集中的所有列值
- flutter - 如何捆绑flutter ffi插件
- python - 为什么 python list remove() 不适用于绘图列表?
- excel - PowerQuery - 基于辅助字段的聚合期间的 List.PositionOf()
- javascript - UnhandledPromiseRejectionWarning:RequestError:错误:无效的URI“0”
- bash - bash检查目录中是否存在给定文件在通配符上失败