使用group by从两个表中减去两列

8dtrkrch 于 2021-06-18 发布在 Mysql

关注(0)|答案(4)|浏览(315)

我有四张table。

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);

现在我想从这些（stockin-stockout）计算库存。
通过使用下面的查询，我通过分组分支从stock表中得到stockin和stockout。
库存

select BranchId, ifnull(sum(Quantity),0) Quantity from stockin where productid=1 group by BranchId;

缺货

select BranchId, ifnull(sum(Quantity),0) Quantity from stockout where productid=1 group by BranchId;

我想这样展示结果

sql mysql Database

来源：https://stackoverflow.com/questions/52474347/subtract-two-columns-from-two-tables-with-group-by

4条答案

按热度按时间

wqsoz72f1#

可以在两个查询之间使用连接

select a.branchId, a.quantity - ifnull(b.quantity,0) result
from (
    select BranchId, ifnull(sum(Quantity),0) Quantity 
    from stockin 
    where productid=1 
    group by BranchId
) a left join (
   Select BranchId, ifnull(sum(Quantity),0) Quantity 
   from stockout
   where productid=1 
   group by BranchId
) b on a.BranchId = b.BranchId

赞(0）回复(0）举报 2021-06-18

rsaldnfx2#

select branchid, sum(quantity) total
from
(
select branchid,quantity from stockin
union all
select branchid, quantity*-1 from stockout
) x
group by branchid;

http://sqlfiddle.com/#!9/c549d3/1号机组

赞(0）回复(0）举报 2021-06-18

uhry853o3#

对于每个单独的select查询结果，获取一个附加字段，即factor。它的值对于入库是+1，对于出库是-1。
使用union all组合各个select查询的结果，并将结果集用作派生表。
现在，简单地再求一次和，乘以因子，在一组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

赞(0）回复(0）举报 2021-06-18

ru9i0ody4#

在入库和出库之间使用左连接，这里需要左连接，因为您可能有入库但可能没有出库
左加入你的两个要求

select t1.branchId, t1.quantity - coalesce(t2.quantity,0) result
from (
    select BranchId, coalesce(sum(Quantity),0) Quantity 
    from stockin 
    where productid=1 
    group by BranchId
) t1 left join (
   Select BranchId, coalesce(sum(Quantity),0) Quantity 
   from stockout
   where productid=1 
   group by BranchId
) t2 on t1.BranchId = t2.BranchId

branchId    result
1            4
2           10

http://sqlfiddle.com/#!9/c549d3/6号

赞(0）回复(0）举报 2021-06-18

我来回答

使用group by从两个表中减去两列

4条答案

相关问题

热门标签

最新问答