如何检查 SQL Server 中的重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4273821/
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 can I check duplicate record in SQL Server
提问by Jin Yong
Does any one know how can I write a SQL Server script to check whether table is contain duplicate phone number?
有谁知道如何编写 SQL Server 脚本来检查表是否包含重复的电话号码？
Example:
例子：
I have a table called customer with following data
我有一个名为 customer 的表，其中包含以下数据
name telephone
alvin 0396521254
alan 0396521425
amy 0396521425
How can I write a script in SQL Server that can return those records with duplicate telephone number??
如何在 SQL Server 中编写一个脚本来返回那些具有重复电话号码的记录？
回答by OMG Ponies
To see values with duplicates:
要查看重复的值：
SELECT c.telephone
FROM CUSTOMER c
GROUP BY c.telephone
HAVING COUNT(*) > 1
To see related records in the table for those duplicates:
要在表中查看这些重复项的相关记录：
SELECT c.*
FROM CUSTOMER c
JOIN (SELECT c.telephone
FROM CUSTOMER c
GROUP BY c.telephone
HAVING COUNT(*) > 1) x ON x.telephone = c.telephone
回答by Adriaan Stander
You can try something like
你可以尝试类似的东西
;WITH Duplicates AS (
SELECT Telephone
FROM Table
HAVING COUNT(1) > 1
)
SELECT t.*
FROm Table t INNER JOIN
Duplicates d ON t.Telephone = d.Telephone
Or even something like
或者甚至像
SELECT *
FROM Table t
WHERE EXISTS (
SELECT 1
FROM Table tI
WHERE tI.Telephone = t.Telephone
AND tI.Name != t.Name
)
回答by Frazell Thomas
I'm sure someone will come up with a better solution, but I'll give my crude way anyway.
我相信有人会想出更好的解决方案，但无论如何我都会给出我粗略的方法。
If it were me I'd use the aggregate COUNT function along with the GROUP BY clause.
如果是我，我会使用聚合 COUNT 函数和 GROUP BY 子句。
I would write an SQL statement such as:
我会写一个 SQL 语句，例如：
SELECT telephone, COUNT(telephone)[Entries] FROM table1
GROUP BY telephone
HAVING Entries > 1
That should return any duplicate phone numbers with a count for how many duplicates exist.
这应该返回任何重复的电话号码，并计算存在多少重复项。
