使用一个字段很容易找到重复项:

SELECT email, 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

这个查询将告诉我们John、Sam、Tom和Tom,因为他们都有相同的电子邮件。

然而,我想要的是获得相同电子邮件和名称的副本。

也就是说,我想得到“汤姆”,“汤姆”。

我需要这个的原因是:我犯了一个错误,允许插入重复的名称和电子邮件值。现在我需要删除/更改重复项,所以我需要先找到它们。


当前回答

select name, email
, case 
when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
else 'No'
end "duplicated ?"
from users

其他回答

派对晚了一点,但我找到了一个非常酷的解决方法来查找所有重复的ID:

SELECT email, GROUP_CONCAT(id)
FROM   users
GROUP  BY email
HAVING COUNT(email) > 1;

另一种简单的方法是使用解析函数:

SELECT * from 

(SELECT name, email,

COUNT(name) OVER (PARTITION BY name, email) cnt 

FROM users)

WHERE cnt >1;
SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

只需在两列上分组。

注意:旧的ANSI标准是在GROUP BY中包含所有非聚合列,但这已经随着“功能依赖性”的思想而改变:

在关系数据库理论中,函数依赖是数据库关系中两组属性之间的约束。换句话说,函数依赖是描述关系中属性之间关系的约束。

支持不一致:

最近的PostgreSQL支持它。SQL Server(截至SQL Server 2017)仍然需要GROUP BY中的所有非聚合列。MySQL是不可预测的,您需要sql_mode=only_full_group_by:GROUP BY lname ORDER BY显示错误结果;在缺少ANY()的情况下,这是最便宜的聚合函数(请参阅公认答案中的注释)。甲骨文还不够主流(警告:幽默,我不了解甲骨文)。

我们可以在这里使用have,它处理聚合函数,如下所示

create table #TableB (id_account int, data int, [date] date)
insert into #TableB values (1 ,-50, '10/20/2018'),
(1, 20, '10/09/2018'),
(2 ,-900, '10/01/2018'),
(1 ,20, '09/25/2018'),
(1 ,-100, '08/01/2018')  

SELECT id_account , data, COUNT(*)
FROM #TableB
GROUP BY id_account , data
HAVING COUNT(id_account) > 1

drop table #TableB

这里有两个字段id_account和data与Count(*)一起使用。因此,它将给出两列中值超过一倍的所有记录。

由于某种原因,我们错误地错过了在SQL server表中添加任何约束,并且记录已在前端应用程序的所有列中重复插入。然后我们可以使用下面的查询从表中删除重复的查询。

SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable

在这里,我们获取了原始表的所有不同记录,并删除了原始表中的记录。我们再次将新表中的所有不同值插入到原始表中,然后删除新表。

这是我想出的最简单的办法。它使用一个公共表表达式(CTE)和一个分区窗口(我认为这些特性在SQL 2008和更高版本中)。

此示例查找所有具有重复名称和dob的学生。要检查重复的字段位于OVER子句中。可以在投影中包含任何其他字段。

with cte (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName, LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LName