在SQL表中查找重复的值？
"一个字段很容易找到重复项：
SELECT name, COUNT(email)
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
但是，如果有下表
ID NAME EMAIL
1 John asd@asd.com
2 Sam asd@asd.com
3 Tom asd@asd.com
4 Bob bob@asd.com
5 Tom asd@asd.com
这个查询会给我们约翰，萨姆，汤姆，汤姆，因为他们都有相同的email。
但是，我想要的是得到相同的重复email 和 name。
那就是我想得到“Tom”，“Tom”。
"
回答 2
尝试这个：
declare @YourTable table (id int, name varchar(10), email varchar(50))INSERT @YourTable VALUES (1,'John','John-email')INSERT @YourTable VALUES (2,'John','John-email')INSERT @YourTable VALUES (3,'fred','John-email')INSERT @YourTable VALUES (4,'fred','fred-email')INSERT @YourTable VALUES (5,'sam','sam-email')INSERT @YourTable VALUES (6,'sam','sam-email')SELECT name,email, COUNT(*) AS CountOf FROM @YourTable GROUP BY name,email HAVING COUNT(*)>1OUTPUT：
name email CountOf---------- ----------- -----------John John-email 2sam sam-email 2(2 row(s) affected)如果你想使用这个dups的ID：
SELECT y.id,y.name,y.email FROM @YourTable y INNER JOIN (SELECT name,email, COUNT(*) AS CountOf FROM @YourTable GROUP BY name,email HAVING COUNT(*)>1 ) dt ON y.name=dt.name and y.email=dt.emailOUTPUT：
id name email----------- ---------- ------------1 John John-email2 John John-email5 sam sam-email6 sam sam-email(4 row(s) affected)删除重复的尝试：
DELETE d FROM @YourTable d INNER JOIN (SELECT y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank FROM @YourTable y INNER JOIN (SELECT name,email, COUNT(*) AS CountOf FROM @YourTable GROUP BY name,email HAVING COUNT(*)>1 ) dt ON y.name=dt.name and y.email=dt.email ) dt2 ON d.id=dt2.id WHERE dt2.RowRank!=1select * FROM @YourTableOUTPUT：
id name email----------- ---------- --------------1 John John-email3 fred John-email4 fred fred-email5 sam sam-email(4 row(s) affected)SELECT name, email, COUNT(*)FROM usersGROUP BY name, emailHAVING COUNT(*) > 1可以参考下如上代码
和开发者交流更多问题细节吧，去 写回答
相关文章
社区富文本编辑器全新改版！诚邀体验～
全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验
腾讯云开发者
