连接两个不同的表并删除重复的条目 [英] join two different tables and remove duplicated entries

问题描述

我是sql查询的新手。我有两个表：

I'm complete novice in sql queries. I have two tables:

表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

表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.

推荐答案

您可以使用 UNION 子句， UNION 将检查重复项，并且仅返回不同的行

You can use UNION clause, UNION will check for duplicates and only distinct rows will be returned

SELECT * FROM table1
UNION
SELECT * FROM Table2

编辑：要存储两个表中的数据而不重复，请执行以下操作

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)

这将从表2中插入与名称不匹配的行，表1中的邮政编码

This will insert rows from table2 that do not match name, postal code from table1

或者，您也可以创建新表，而不触摸table1和table2

Alternative is that You can also create new table and not touch table1 and table2

CREATE TABLE TABLENAME AS
SELECT * FROM table1
UNION
SELECT * FROM Table2

这篇关于连接两个不同的表并删除重复的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！