postgresql 合并两个列名相同的表，添加计数器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5162468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merge two tables with same column names, add counters
提问by evgeni
I have two tables with the same columns, the first column is the name and the second is a count. I would like to merge these tables, so that each name appears with the added count of the two tables:
我有两个具有相同列的表，第一列是名称，第二列是计数。我想合并这些表，以便每个名称都与两个表的相加计数一起出现：
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
As of the moment I have created a pretty ugly structure to execute this, and would?like to know if it is possible to get the results in a more elegant way.
到目前为止，我已经创建了一个非常丑陋的结构来执行此操作，并且想知道是否有可能以更优雅的方式获得结果。
What I have so far (Table1 is test1 and Table2 is test2):
到目前为止我所拥有的（表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;
回答by Reece
=> 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)
回答by Shay Rojansky
How about this, in pure SQL:
怎么样，在纯 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;
Basically we're doing a full outer join on the name field to merge the two tables. The tricky part is that with the full outer join, rows that exist in one table but not the other will appear, but will have NULL in the other table; so if t1 has "name1" but t2 doesn't, the join will give us NULLs for t2.name and t2.name.
基本上，我们正在对名称字段进行完整的外部联接以合并两个表。棘手的部分是，对于全外连接，存在于一个表中但不在另一个表中的行将出现，但在另一个表中将具有 NULL；因此，如果 t1 有“name1”但 t2 没有，则连接将为我们提供 t2.name 和 t2.name 的 NULL。
The COALESCE function returns the first non-NULL argument, so we use it to "convert" the NULL counts to 0 and to pick the name from the correct table. Thanks for the tip on this Wayne!
COALESCE 函数返回第一个非 NULL 参数，因此我们使用它来将 NULL 计数“转换”为 0 并从正确的表中选择名称。感谢您对这个韦恩的提示！
Good luck!
祝你好运！
回答by user1460856
An alternative method is to use the NATURAL FULL OUTER JOIN combined with SUM(count) and GROUP BY name statements. The following SQL code exactly yields the desired result:
另一种方法是使用 NATURAL FULL OUTER JOIN 结合 SUM(count) 和 GROUP BY 名称语句。以下 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
The artificial tableidcolumn ensures that the NATURAL FULL OUTER JOIN creates a separate row for each row in t1 and for each row in t2. In other words, the rows "name3, 3" and "name4, 4" appear twice in the intermediate result. In order to merge these duplicate rows and to sum the counts we can group the rows by the name column and sum the count column.
人工tableid列确保 NATURAL FULL OUTER JOIN 为 t1 中的每一行和 t2 中的每一行创建一个单独的行。换句话说，“name3, 3”和“name4, 4”行在中间结果中出现了两次。为了合并这些重复的行并对计数求和，我们可以按名称列对行进行分组并对计数列求和。
