mysql - 如何将月销售和月采购表合并到一个表中？

Question

我有两个表：月销售额（月，total_sales）和月购买（月，total_purchase）。我需要将表格和输出（月、总销售额、总购买量）结合起来。

Month_Sales:       Monthly_Purchase:
+----+----------+  +-----+-------------+
| Month | sales |  |  Month | purchase |
+----+----------+  +-----+-------------+
| Jan  | 50000  |  | Jan    | 50000    |
| Mar  | 20000  |  | Feb    | 60000    |
| Jun  | 10000  |  | Mar    | 40000    |
+----+----------+  +-----+-------------+

输出：

+----+----------+---------+
| Month | sales | purchase|
+----+----------+---------+
| Jan  | 50000  | 50000   |
| Feb  |  NULL  | 60000   |
| Mar  | 20000  | 40000   |
| Jun  | 10000  | NULL    |
+----+----------+---------+

我尝试使用 FULL OUTER JOIN 来实现这一点，但它没有提供预期的效果。

SELECT Table1.month, Table1.sales, Table2.purchase FROM (SELECT month, sales from Monthly_Sales) as Table1
FULL OUTER JOIN (SELECT month, purchase from Monthly_Purchase) as Table2
ON Table1.month = Table2.month;

所以我该怎么做？

标签： mysqlsqljoinmergeouter-join

Answer 1

您可以使用union all和group by：

select month, sum(sales), sum(purchase)
from ((select month, sales, null as purchase
       from sales
      ) union all
      (select month, null, purchase
       from purchases
      )
     ) sp
group by month;