mysql - 如何组合来自差异表的 2 个查询

Question

我有 2 个疑问。第一个是找到 Netty ，第二个是预算总和。我需要显示结果，包括来自差异表的商店名称和商店编号。

这是我查找总净金额的第一个查询:

SELECT sum(a.netamt) as netamt, b.store_name 
  FROM site_sales a JOIN site_store b ON b.storenum = a.storenum 

  WHERE a.busidate >= '2017-01-01' AND a.busidate <='2017-04-30'
  GROUP BY a.storenum

第二个查询是查找总预算

SELECT

SUM(CASE WHEN c.busidate BETWEEN '2017-01' AND '2017-04' THEN c.budget ELSE 0 END) as budget,
b.store_name
FROM site_kpimthslsbgt c JOIN site_store b ON b.storenum = c.storenum 
GROUP BY c.storenum

我需要结合这两个查询。输出的结果应该是 like this

Answer 1

尝试这个如果您的单个查询中提取了多个记录，您还需要在最后设置连接条件:

Select x.netamt, y.budget, y.store_name
from
(
SELECT sum(a.netamt) as netamt, b.store_name 
FROM site_sales a JOIN site_store b ON b.storenum = a.storenum 
WHERE a.busidate >= '2017-01-01' AND a.busidate <='2017-04-30'
GROUP BY a.storenum
) x
inner join
(
SELECT
SUM(CASE WHEN c.busidate BETWEEN '2017-01' AND '2017-04' THEN c.budget ELSE 0 END) as budget,
b.store_name
FROM site_kpimthslsbgt c JOIN site_store b ON b.storenum = c.storenum 
GROUP BY c.storenum
) y
on x.store_name = y.store_name