Mysql join 与 left join 和 group by

Question

我想组合三个 sql 查询，下面给出查询，site 和 goods 列是公共(public)列，我的结果中想要 9 列，如果你一一运行这三个查询，你将得到 3 个结果，有 3 列,我想结合这三个结果

select distinct
  a.site,a.goods,
  sum(a.quantity)
from
  stock_consumed a
group by
  a.site,a.goods


select distinct
  b.site,
  b.goods,sum(b.quantity)
from
  opening_balance b
group by
  b.site,b.goods


select distinct
  c.site,
  c.goods,sum(c.no_of_units)
from
  inward_stock c
group by
  c.site,c.goods

Answer 1

您正在寻找union。它允许您合并两个查询的结果，只要它们的结果具有相同的列数。

select distinct a.site,a.goods,sum(a.quantity) from stock_consumed a group by a.site,a.goods
union 
select distinct b.site,b.goods,sum(b.quantity) from opening_balance b group by b.site,b.goods
union
select distinct c.site,c.goods,sum(c.no_of_units) from inward_stock c group by c.site,c.goods