最近在测试需求时，遇到了一个难题，需要计算的字段来自不同的数据表，每次都要单独从没个表查出来再手动加和或者进行其他运算。后来花了点时间写了一个sql，能够解决我的问题。

一、问题

  从A表中查出A.a、A.b两个字段，从B表中查出B.x字段，从C表中查出C.c字段，从D表中查出D.d字段，然后求A.a+A.b+B.x+C.c+D.d的和。要求查询时各个表的查询条件（包含where条件和group by 条件都相同）。

二、解决办法

原本想使用全连接，但是mysql（我使用的Navicat）不支持:

SELECT * FROM  (( 
SELECT business_date,( box_count_all ) '当期妥投件数' FROM daily_operation_indicators WHERE business_date BETWEEN "2022-02-11" AND "2022-02-28" ) x
full JOIN 
( SELECT business_date, sum( operating_other_cost ) FROM daily_labor_cost WHERE business_date BETWEEN "2022-02-11" AND "2022-02-28" GROUP BY business_date ) y 
ON x.business_date = y.business_date )

后来换了一种方法，使用union来实现，实现sql如下：

select business_date,sum(x)+sum(y)+sum(w)+sum(z) sum_cost,sum(u) sum_box from (
select war_zone_name,war_zone_code ,business_date,sum(housing_cost) +sum(materials_cost) 'x',0 'y',0 'w',0 'z',0 'u'from daily_labor_cost where business_date between "2021-09-02" and "2022-02-28" group by business_date,war_zone_name,war_zone_code  union all 

select war_zone_name,war_zone_code ,business_date,0 'x',sum(transport_cost) 'y',0 'w',0 'z',0 'u'from A where business_date between "2021-09-02" and "2022-02-28" group by business_date,war_zone_name,war_zone_code   union all 

select war_zone_name,war_zone_code ,business_date,0 'x',0 'y',sum(transport_cost) 'w',0 'z',0 'u'from B where business_date between "2021-09-02" and "2022-02-28" group by business_date,war_zone_name,war_zone_code   union all 

select war_zone_name,war_zone_code ,business_date,0 'x',0 'y',0 'w',sum(heavycargo_subsidy)+sum(grid_station_cost) 'z',0 'u' from C where business_date between "2021-09-02" and "2022-02-28" group by business_date,war_zone_name,war_zone_code  union all 

select war_zone_name,war_zone_code ,business_date,0 'x',0 'y',0 'w', 0'z',sum(box_count_all) 'u' from D where business_date between "2021-09-02" and "2022-02-28" group by business_date,war_zone_name,war_zone_code 

) t  
group by business_date ,war_zone_name,war_zone_code ;