SQL 如何在表中查找重复名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20094055/
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 duplicate names in a table
提问by Mouzzam Hussain
I have a table having person names address and job title. the names are repeating sometimes. I have to compare if two people have the same name and same address than i have to keep only 1 record of them.
我有一张表，上面有人名地址和职位。名字有时会重复。我必须比较两个人是否有相同的名字和相同的地址，而不是我只需要保留他们的 1 条记录。
Table: Data_Excel
Name: P_Name
Address: P_Address
City: P_city
回答by Filipe Silva
To find the duplicates you can do:
要查找重复项，您可以执行以下操作：
SELECT P_name,
P_Address,
P_city
FROM Data_Excel
GROUP BY P_Name,
P_Address,
P_city
HAVING COUNT(*) > 1;
To remove duplicates you could do:
要删除重复项，您可以执行以下操作：
DELETE
FROM Data_Excel
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM Data_Excel
GROUP BY P_Name,
P_Address,
P_city
);
To Insert in Person table you would do:
要在 Person 表中插入，您可以执行以下操作：
INSERT INTO Person(id,name)
SELECT (SELECT MAX(id)+1 FROM Person),P_Name
FROM Data_Excel WHERE P_Name NOT IN (SELECT name FROM Person)
回答by SajjadHashmi
SELECT P_Name,P_Address,count(*)
FROM Data_Excel
GROUP BY P_Name,P_Address
HAVING count(*) > 1;
This will give you the records with same P_Name& P_Address.
这将为您提供相同P_Name&的记录P_Address。
