mysql - 模拟全连接返回错误值

Question

我有两个没有关系的表，我进行左连接和右连接来模拟它们的完全连接并选择一些数据。
显示数据的方式是正确的，但值是错误的，看起来他们选择了不止一次。
我的 table 是这样的:
表 1(特价)
trade_date ---- 利润

表 2(一般成本)
日期-----成本

这是我写的查询:

select b.trade_date, coalesce(sum(b.profit),0),  coalesce(sum(g.cost),0)  
from bargains as b 
left join general_cost as g on b.trade_date =  g.date group by b.trade_date 
union
select g.date, coalesce(sum(b.profit),0), coalesce(sum(g.cost),0) from
bargains as b
right join general_cost as g on b.trade_date =  g.date  group by g.date  

这是查询的结果:

例如，在日期 1395-9-28 中，利润列的总和应为 440，成本列的总和应为 800
如果有帮助，您应该知道，便宜货表中有包含此日期的三行，general_cost 表中有两行

Answer 1

是的，您的查询重复了匹配记录，因为这些记录同时包含在左连接和右连接中。您需要从其中一个查询中排除匹配的记录。我通常将它们从联合的第二个查询中排除:

select b.trade_date, coalesce(sum(b.profit),0),  coalesce(sum(g.cost),0)  
from bargains as b 
left join general_cost as g on b.trade_date =  g.date group by b.trade_date 
union
select g.date, coalesce(sum(b.profit),0), coalesce(sum(g.cost),0) from
bargains as b
right join general_cost as g on b.trade_date =  g.date
where b.date is null //include only the records from general_cost that are not matched 
group by g.date

更新

如果两个表中有多个具有相同日期的记录，则需要在子查询中分别对每个表进行求和，否则匹配的记录会重复:

select b.trade_date, b.profit,  coalesce(g.cost,0)  
from (select trade_date, sum(profit) as profit from bargains group by trade_date) as b 
left join (select date, sum(cost) as cost from general_cost group by date) as g on b.trade_date =  g.date
union
select g.date, 0, sum(g.cost) from //all profits has been summed up in the above query, so here we can use 0 in place of profit
bargains as b
right join general_cost as g on b.trade_date =  g.date
where b.trade_date is null //include only the records from general_cost that are not matched 
group by g.date