sql - UNION ALL 具有不同列名的表

Question

我有两个相似的表，我试图将它们加入一个联合，然后可能分组依据。我希望在表格不重叠的地方添加一列 null 或 0。

SELECT count(traffic_volume_1) as traffic_volume_1, 
       traffic_source, 
       timestamp
FROM table_1
UNION ALL 
       count(traffic_volume_2) as traffic_volume_2, 
       traffic_source, 
       timestamp
FROM table_2
...?

我正在寻找如下所示的返回:

traffic_volume_1, traffic_volume_2, timestamp, traffic_source
77777           , 0               , 2018-02-09, US
0               , 928320          , 2018-02-09, EU

有什么想法吗？

Answer 1

在 UNION 的两半中为 0 值列添加一个占位符:

SELECT count(traffic_volume_1) as traffic_volume_1, 
       0 as traffic_volume_2,
       traffic_source, 
       timestamp
FROM table_1
GROUP BY traffic_source, 
         timestamp
UNION ALL 
       0 as traffic_volume_1,
       count(traffic_volume_2) as traffic_volume_2, 
       traffic_source, 
       timestamp
FROM table_2
GROUP BY traffic_source, 
         timestamp