SQL 连接两个不同的表并删除重复的条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14303573/
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
join two different tables and remove duplicated entries
提问by Chris
I'm complete novice in sql queries. I have two tables:
我是 sql 查询的完全新手。我有两个表：
table1:
表格1：
id_s name post_code city subject
------------------------------------------
1 name1 postal1 city1 subject1
2 name2 postal2 city2 subject2
3 name3 postal3 city3 subject3
4 name4 postal4 city4 subject4
...
~350
table2:
表2：
id_p name post_code city subject
------------------------------------------
1 name1 postal1 city1 subject1
2 name2 postal2 city2 subject2
3 name3 postal3 city3 subject3
4 name4 postal4 city4 subject4
...
~1200
I want to join both tables, and remove entries with same name and postal code. I found some answers on how to do it but they were too complicated.
我想加入两个表，并删除具有相同名称和邮政编码的条目。我找到了一些关于如何做的答案，但它们太复杂了。
回答by rs.
You can use UNIONclause, UNIONwill check for duplicates and only distinct rows will be returned
您可以使用UNION子句，UNION将检查重复项并且只返回不同的行
SELECT * FROM table1
UNION
SELECT * FROM Table2
Edit: To store data from both table without duplicates, do this
编辑：要存储两个表中没有重复的数据，请执行此操作
INSERT INTO TABLE1
SELECT * FROM TABLE2 A
WHERE NOT EXISTS (SELECT 1 FROM TABLE1 X
WHERE A.NAME = X.NAME AND
A.post_code = x.post_code)
This will insert rows from table2 that do not match name, postal code from table1
这将插入 table2 中与 table1 中的名称和邮政编码不匹配的行
Alternative is that You can also create new table and not touch table1 and table2
替代方法是您还可以创建新表而不是触摸 table1 和 table2
CREATE TABLE TABLENAME AS
SELECT * FROM table1
UNION
SELECT * FROM Table2
回答by Saju
You can give a SELECT INTOcommand like this
你可以给出这样的SELECT INTO命令
SELECT * INTO newtable FROM table1
UNION
SELECT * FROM table2;
This will create a newtable from both table1 and table2 without any duplicates
这将从 table1 和 table2 创建一个没有任何重复项的新表
回答by bhupesh
Try this simple one:
试试这个简单的：
(select * from table1 MINUS select * from table2)
UNION
(select * from table2 MINUS select * from table1)
