按日期把两张桌子连在一起

Question

我有两个表需要连接，并且需要获得可以用来绘制的数据。

两个表格的样本数据为：

**table1**
month       planned_hours
2019-10-01  24
2020-01-01  67
2019-02-01  12
**table2**
date         hrs_consumed
2019-12-07   7
2019-09-09   3
2019-10-12   4
2019-02-01   5
2019-10-11   4

我尝试了以下查询：

SELECT 
a.date as month1,
a.hrs as planned_hours,
a.mon_hrs as actual_hours 
FROM (
SELECT 
sum(t1.hrs_consumed) as hrs,
t1.date,
t2.mon_hrs
FROM booking t1
LEFT JOIN monthly_plan t2 
ON month(t1.date) = month(t2.mon)
UNION
SELECT 
t2.mon_hrs,
t2.mon as date,
sum(t1.hrs_consumed) as hrs
FROM booking t1
RIGHT JOIN monthly_plan t2 
ON month(t1.date) = month(t2.mon)) a

我从查询中得到以下输出：

month1      planned_hrs  actual_hrs
2019-10-01  24           8
2019-10-01  24           8

我想要得到的输出是：

month1   planned_hrs   actual_hrs
10-2019  24            8
12-2019  0             7
08-2019  0             3
01-2020  67            0
02-2019  12            5

Answer 1

您可以将不同的月份加入表格并聚合：

SELECT 
t.month,
COALESCE(MAX(t1.planned_hours), 0) planned_hours,
COALESCE(SUM(hrs_consumed), 0) actual_hrs
FROM (
SELECT DATE_FORMAT(month, "%m-%Y") month FROM table1
UNION
SELECT DATE_FORMAT(date, "%m-%Y") FROM table2
) t
LEFT JOIN table1 t1 ON t.month = DATE_FORMAT(t1.month, "%m-%Y")
LEFT JOIN table2 t2 ON t.month = DATE_FORMAT(t2.date, "%m-%Y")
GROUP BY t.month

请参阅演示
结果：

| month   | planned_hours | actual_hrs |
| ------- | ------------- | ---------- |
| 01-2020 | 67            | 0          |
| 02-2019 | 12            | 5          |
| 09-2019 | 0             | 3          |
| 10-2019 | 24            | 8          |
| 12-2019 | 0             | 7          |