Mysql - 根据月份日期合并两个表

Question

在 mysql 数据库中，我有两个不同的表 s1 和 s2:

s1
event_date  quantity
2014-01-02  200
2014-01-05  400
2014-02-10  200
2014-02-13  300

s2
Time        Temperature     Humidity
2014-01-01  12              60
2014-01-02  14              80
2014-01-03  12              60
2014-01-04  14              80
2014-01-05  12              60
2014-01-06  16              80
2014-01-07  20              60
2014-01-08  14              80
2014-02-01  13              60
2014-02-02  15              80
2014-02-03  16              60
2014-02-04  18              80
2014-02-05  12              60
2014-02-06  17              80
2014-02-07  28              60
2014-02-08  14              80

基本上，我需要将下面的两个“选择”合并到一个新表中，其中数据按月分组:

select date_format(s1.`event_date`,'%Y %M')as Date,SUM(s1.`quantity`) as AvgQuantity from s1
GROUP BY year(s1.`event_date`), month(s1.`event_date`);


select date_format(s2.`Time`,'%Y %M')as Date, avg(s2.`Temperature`) as AvgTemp, avg(s2.`Humidity`) as AvgHum from s2
GROUP BY year(s2.`Time`), month(s2.`Time`);

我需要创建一个像这样的新表:

s1+s2

Date    quantity    Temperature Humidity
2014-01     600         14,25       70
2014-02     500         16,62       70
2014-03 
2014-04

Answer 1

您可以尝试以下 -

select date,sum(AvgQuantity),sum(AvgTemp) as AvgTemp, sum(AvgHum) as AvgHum
from
(
select date_format(s1.`event_date`,'%Y %M')as Date,SUM(s1.`quantity`) as AvgQuantity,0 as AvgTemp,0 as  AvgHum
from s1 
GROUP BY year(s1.`event_date`), month(s1.`event_date`);
union
select date_format(s2.`Time`,'%Y %M')as Date, 0,avg(s2.`Temperature`) as AvgTemp, avg(s2.`Humidity`) as AvgHum from s2
GROUP BY year(s2.`Time`), month(s2.`Time`)
)A group by date