合并两个具有相同列名的表，添加计数器

Question

我有两个具有相同列的表，第一列是名称，第二列是计数。我想合并这些表，以便每个名称都显示两个表的添加计数：

Table1:           Table2:            Result Table:
NAME   COUNT      NAME   COUNT       NAME   COUNT
name1  1          name3  3           name1  1
name2  2          name4  4           name2  2
name3  3          name5  5           name3  6
name4  4          name6  6           name4  8
                                     name5  5
                                     name6  6

到目前为止，我已经创建了一个非常丑陋的结构来执行此操作，并且想知道是否有可能获得以更优雅的方式产生结果。

到目前为止我所拥有的（表1是test1，表2是test2）：

create table test1 ( name varchar(40), count integer);
create table test2 ( name varchar(40), count integer);
create table test3 ( name varchar(40), count integer);
create table test4 ( name varchar(40), count integer);
create table test5 ( name varchar(40), count integer);

insert into test4 (name, count) select *  from test1;
insert into test4 (name, count) select *  from test2;
insert into test3 (name , count) select t1.name, t1.count + t2.count 
from test1 t1 inner join test2 t2 on t1.name = t2.name;
select merge_db(name, count) from test3;
insert into test5 (name, count) (select name, max(count) from test4 group by name);


CREATE FUNCTION merge_db(key varchar(40), data integer) RETURNS VOID AS
    $$ -- souce: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
    BEGIN
        LOOP
            -- first try to update the key
            UPDATE test4 SET count = data WHERE name = key;
            IF found THEN
                RETURN;
            END IF;-- not there, so try to insert the key -- if someone else inserts the same key concurrently,        -- we could get a unique-key failure
            BEGIN
                INSERT INTO test4(name,count) VALUES (key, data);
                RETURN;
            EXCEPTION WHEN unique_violation THEN-- do nothing, and loop to try the UPDATE again
            END;
        END LOOP;
    END;
    $$
    LANGUAGE plpgsql;

Answer 1

=> create table t1 (name text,cnt int);
=> create table t2 (name text,cnt int);
=> insert into t1 values  ('name1',1), ('name2',2), ('name3',3), ('name4',4);
=> insert into t2 values  ('name3',3), ('name4',4), ('name5',5), ('name6',6);
=> 

select name,sum(cnt) from 
(select * from t1 
union all 
select * from t2 ) X 
group by name 
order by 1;

 name  | sum 
-------+-----
 name1 |   1
 name2 |   2
 name3 |   6
 name4 |   8
 name5 |   5
 name6 |   6
(6 rows)

Answer 2

怎么样，在纯 SQL 中：

SELECT
  COALESCE(t1.name, t2.name),
  COALESCE(t1.count, 0) + COALESCE(t2.count, 0) AS count
FROM t1 FULL OUTER JOIN t2 ON t1.name=t2.name;

基本上，我们在名称字段上执行完整的外部联接来合并两个表。棘手的部分是，使用完全外连接时，一个表中存在但另一个表中不存在的行将会出现，但在另一个表中将具有 NULL；因此，如果 t1 有“name1”但 t2 没有，则连接将为 t2.name 和 t2.name 提供 NULL。

COALESCE 函数返回第一个非 NULL 参数，因此我们使用它将 NULL 计数“转换”为 0 并从正确的表中选择名称。感谢韦恩提供的建议！

祝你好运！

Answer 3

另一种方法是结合使用 NATURAL FULL OUTER JOIN 和 SUM(count) 和 GROUP BY name 语句。以下 SQL 代码准确地产生了所需的结果：

SELECT name, SUM(count) AS count FROM
  ( SELECT 1 AS tableid, * FROM t1 ) AS table1 
NATURAL FULL OUTER JOIN
  ( SELECT 2 AS tableid, * FROM t2 ) AS table2
GROUP BY name ORDER BY name

人工 tableid 列确保 NATURAL FULL OUTER JOIN 为 t1 中的每一行和 t2 中的每一行创建单独的行。换句话说，行“name3, 3”和“name4, 4”在中间结果中出现了两次。为了合并这些重复的行并对计数求和，我们可以按名称列对行进行分组并对计数列求和。