SQL 如何从两个表中查找重复项并在其本身中查找重复项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14556737/
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
How to find duplicates from two tables and also to find duplicate in itself?
提问by 10e5x
I have created this statement in access 2003
我在 access 2003 中创建了这个语句
SELECT COUNT(*)
FROM TABLEA
WHERE NOT EXISTS(SELECT * FROM TABLEB);
Does this statement helps to check if the records in table A is the same as table b? TABLEA is the new table of table b and i want to make sure that all records from table b are in table A.
此语句是否有助于检查表 A 中的记录是否与表 b 中的记录相同？TABLEA 是表 b 的新表，我想确保表 b 中的所有记录都在表 A 中。
Secondly i have this table TABLEC. How can i check if there are duplicate records, meaning all the fields values are the same, in TABLEC?
其次，我有这张桌子 TABLEC。如何检查 TABLEC 中是否存在重复记录，这意味着所有字段值都相同？
回答by alzaimar
The answer is: No, your query does not make sense.
答案是：不，您的查询没有意义。
To tell whether two records are 'same', you have to define the term 'equal'. Should all fields be equal? Or only certain fields?
要判断两条记录是否“相同”，您必须定义术语“相等”。所有字段都应该相等吗？还是只有某些领域？
If you have two Tables TableAand TableBand they have two fields 'A' and 'B', then this statement finds all records which exist in both tables:
如果您有两个表TableA，TableB并且它们有两个字段“A”和“B”，则此语句将查找存在于两个表中的所有记录：
select distinct TableA.*
from TableA
join TableB
on TableA.A = TableB.A
and TableA.B = TableB.B
Or
或者
select *
from TableA
where exists (
select 1
From TableB
where TableA.A = TableB.A
and TableA.B = TableB.B
)
Edit: User 10e5x pointed out that his table contains NULL values. So the comparison per field has to be a bit more complicated to compensate the NULL comparison caveats.
编辑：用户 10e5x 指出他的表包含 NULL 值。因此，每个字段的比较必须更复杂一些，以补偿 NULL 比较警告。
I will just give the WHEREpart:
我只会给出WHERE部分：
where TableA.A = TableB.A or coalesce (TableA.A, TableB.A) is NULL
and TableA.B = TableB.B or coalesce (TableA.B, TableB.B) is NULL
The function coalesce(a,b,c...)returns the leftmost non NULL value, hence
该函数coalesce(a,b,c...)返回最左边的非 NULL 值，因此
coalesce (A,B) is NULL
-- is equal to
A is NULL and B is NULL
Note: This tricky coding is a reason why you should avoid NULL values in columns which are used for comparison.
注意：这种棘手的编码是您应该避免在用于比较的列中使用 NULL 值的原因。
回答by user3801029
Try this
尝试这个
SELECT documentno FROM TableA INTERSECT SELECT documentno FROM TableB
SELECT documentno FROM TableA INTERSECT SELECT documentno FROM TableB
