mysql - 将所有 5 个表值相加以形成新表？

Question

我有 5 个具有以下属性的表，

+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| actor_id           | int(11)     | YES  | MUL | NULL    |       |
| activity_object_id | int(11)     | YES  | MUL | NULL    |       |
| interest_level     | tinyint(4)  | YES  |     | 10      |       |
| feed_view          | smallint(6) | YES  |     | 0       |       |
| quick_view         | smallint(6) | YES  |     | 0       |       |
| page_view          | smallint(6) | YES  |     | 0       |       |
| fullscreen_view    | smallint(6) | YES  |     | 0       |       |
| reserved1          | int(11)     | YES  |     | NULL    |       |
| reserved2          | int(11)     | YES  |     | NULL    |       |
| reserved3          | int(11)     | YES  |     | NULL    |       |
| created_at         | datetime    | YES  |     | NULL    |       |
| updated_at         | datetime    | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+

我们如何创建新的临时表，它是所有 5 个表值的总和。 activity_object_id 是唯一的，一个表可能包含activity_object_id，而另一个表可能不包含。

table1 有一个 active_object_id 为“gowthamkey”，table2 有相同的键“gowthamkey”，而 table3 可能没有“gowthamkey”。因此，我想将所有表值汇总到新表中，以便它有一个键“gowthamkey”，其中值是以下值的总和:

feed_view、quick_view、page_view、fullscreen_view、reserved1、reserved2、reserved3 除了 actor_id、interest_level、created_at、updated_at。

这是我根据 @bummer 回答提出的查询:

CREATE TABLE actor_activity_object_stats_temp_7_days_12 AS 
select actor_id, activity_object_id, interest_level, SUM(feed_view) AS feed_view, SUM(quick_view) AS quick_view, SUM(fullscreen_view) as fullscreen_view 
from (
    select * from actor_activity_object_stats_temp_2016_04_29 
    union all, 
    select * from actor_activity_object_stats_temp_2016_04_30 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_01 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_02 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_03 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_04 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_05 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_06 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_07 
    union all, 
    select * from actor_activity_object_stats_temp_2016_05_08 ) AS X
group by activity_object_id

Answer 1

从 UNION 开始，然后使用 SUM() 添加所有表中同一 activity_object_id 的所有值。

CREATE TABLE new_table
AS SELECT activity_object_id, SUM(feed_view) AS feed_view, SUM(quick_view) AS quick_view, ...
    FROM (SELECT * FROM table1
          UNION ALL
          SELECT * FROM table2
          UNION ALL
          SELECT * FROM table3
          ...) AS x
    GROUP BY activity_object_id