MySQL查询多个表

Question

您好!我有 12 张表，一年中的每个月一张:

January

+----+-----+  
| id | sale|  
+----+-----+  
|  1 | 250 |  
|  3 | 500 |
|  5 | 200 |  
|  7 | 100 |  
+----+-----+

February

+----+-----+  
| id | sale|  
+----+-----+  
|  1 | 350 |  
|  2 | 400 |
|  3 | 500 |  
|  4 | 800 |  
+----+-----+

等等

我需要做一个查询，结果是这样的:

Annual Sales
+----+-----------+-----------+
| id | Sales_Jan | Sales_Feb |
+----+-----------+-----------+
|  1 |       250 |       350 |
|  2 |         0 |       400 |
|  3 |       500 |       500 |
|  4 |         0 |       800 |
|  5 |       200 |         0 |
|  7 |       100 |         0 |
+----+-----------+-----------+

如果两个表中的匹配 ID 不重复，并且其他月份缺少的 ID 通过放置 0 或任何其他符号来表示该 ID 该月没有任何销售。

非常感谢!

Answer 1

您可以使用union all 和聚合来解决这个问题:

select id,
       sum(case when month = 'Jan' then sale else 0 end) as Jan_Sale,
       sum(case when month = 'Feb' then sale else 0 end) as Feb_Sale,
       . . .
       sum(case when month = 'Dec' then sale else 0 end) as Dec_Sale
from ((select 'Jan' as month, id, sale from January) union all
      (select 'Feb' as month, id, sale from February) union all
      . . .
      (select 'Dec' as month, id, sale from February)
     ) t
group by id;