在SQL表中查找重复值

Question

如果只有一个字段，很容易找到重复项：

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

所以，如果我们有一个表格

ID   NAME   EMAIL
1    John   [e-mail]
2    Sam    [e-mail]
3    Tom    [e-mail]
4    Bob    [e-mail]
5    Tom    [e-mail]

这个查询会返回John、Sam、Tom、Tom，因为他们的email相同。

但是，我想要获取具有相同email 和 name的重复项。

也就是说，我想得到"Tom"、"Tom"。

我需要这样做的原因是：我犯了一个错误，允许插入重复的name和email值。现在我需要删除/更改重复项，所以我需要先找到它们。

- Alex

---

我认为在你的第一个示例中，它不会让你选择名称，因为它不在聚合函数中。"匹配电子邮件地址及其姓名的数量是多少" 是一些棘手的逻辑... - sXe

发现由于SELECT中的“name”字段，该方法无法与MSSQL服务器一起使用。 - E. van Putten

Answer 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：
  • 按 lname 分组 ORDER BY 显示错误结果；
  • 在没有 ANY() 的情况下最便宜的聚合函数是什么（请参见接受答案中的注释）。
• Oracle 不够主流（警告：幽默，我不知道 Oracle）。

- gbn

---

@webXL WHERE 用于单条记录，HAVING 用于分组。 - bjan

能否在结果中包含ID？这样之后删除重复项就更容易了。 - user797717

你需要使用MIN(ID)并删除不在最后的ID值，如果MIN(ID)的值。 - gbn

如果任何一列具有空值，该怎么办？ - Ankit Dhingra

非常感谢您提供的信息，是的，它确实可以在Oracle中工作，虽然我需要条件的唯一性，所以与其使用>1，我需要改为=1。 - Bill Naylor

Answer 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(*)>1

输出：

name       email       CountOf
---------- ----------- -----------
John       John-email  2
sam        sam-email   2

(2 row(s) affected)

如果你想要重复项的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.email

输出：

id          name       email
----------- ---------- ------------
1           John       John-email
2           John       John-email
5           sam        sam-email
6           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!=1
SELECT * FROM @YourTable

输出：

id          name       email
----------- ---------- --------------
1           John       John-email
3           fred       John-email
4           fred       fred-email
5           sam        sam-email

(4 row(s) affected)

- KM.

---

表名区分大小写。array(3) { [0]=> string(5) "42000" [1]=> int(1064) [2]=> string(226) "您的SQL语法存在错误，请检查与您的MySQL服务器版本相对应的手册，以获取正确的语法使用方式，位于第1行附近：'(PARTITION BY y.employee_id, y.leave_type_id ) AS RowRank'" } - JAMZAD

Answer 3

试试这个：

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

- Chris Van Opstal

Answer 4

如果您想删除重复项，这里有一个比在三重子查询中查找奇偶行要简单得多的方法：

SELECT id, name, email 
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id

因此要进行删除：

DELETE FROM users
WHERE id IN (
    SELECT id/*, name, email*/
    FROM users u, users u2
    WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)

在我看来，更容易阅读和理解

注意: 唯一的问题是您必须执行请求，直到没有行被删除，因为您每次只删除重复项中的一个

- Tancrede Chazallet

---

很好读；我希望找到一种方法，可以一次性删除多个重复行。 - Dickon Reed

这对我不起作用，因为我得到了“您不能在FROM子句中更新目标表'users'”的错误。 - Whitecat

@Whitecat 看起来是一个简单的 MySQL 问题：https://dev59.com/7m855IYBdhLWcg3wSiM7 - Tancrede Chazallet

对我来说失败了。我得到了：“DBD::CSV::st执行失败：在/Users/hornenj/perl5/perlbrew/perls/perl-5.26.0/lib/site_perl/5.26.0/SQL/Eval.pm的第43行中，使用未初始化的值$ _ [1]作为哈希元素。” - Nigel Horne

我认为 where 子句应该是 "u.name = u2.name AND u.email = u2.email AND (u.id > u2.id OR u2.id > u.id)"，不是吗？ - GiveEmTheBoot

Answer 5

与其他答案不同的是，如果有的话，您可以查看包含所有列的整个记录。在row_number函数的PARTITION BY部分中选择所需的唯一/重复列。

SELECT  *
FROM    (
 SELECT a.*
 ,      Row_Number() OVER (PARTITION BY Name, Age ORDER BY Name) AS r
 FROM   Customers AS a
)       AS b
WHERE   r > 1;

当您想选择所有重复记录和所有字段时，可以这样编写：

CREATE TABLE test (
        id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
,       c1      integer
,       c2      text
,       d       date DEFAULT now()
,       v       text
);

INSERT INTO test (c1, c2, v) VALUES
(1, 'a', 'Select'),
(1, 'a', 'ALL'),
(1, 'a', 'multiple'),
(1, 'a', 'records'),
(2, 'b', 'in columns'),
(2, 'b', 'c1 and c2'),
(3, 'c', '.');
SELECT * FROM test ORDER BY 1;

SELECT  *
FROM    test
WHERE   (c1, c2) IN (
 SELECT c1, c2
 FROM   test
 GROUP  BY 1,2
 HAVING count(*) > 1
)
ORDER   BY 1;

在PostgreSQL中测试通过。

- gaurav singh

---

将 SELECT * 稍作修改，帮助我解决了一个小时的搜索。我以前从未使用过 OVER(PARTITION BY)。SQL 中有很多种做同一件事的方式，令人惊叹不已！ - Joe Ruder

Answer 6

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

- PRADEEPTA VIRLLEY

Answer 7

虽然我来晚了，但我发现一个非常酷的解决方案来查找所有重复的ID：

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

- Indivision Dev

---

看起来是一种语法糖的解决方法。不错的发现。 - Chef_Code

请记住，GROUP_CONCAT 会在一定长度后停止，因此您可能无法获取所有的 id。 - v010dya

Answer 8

这个语句选择/删除除每组重复中的一个记录外的所有重复记录。因此，删除操作会保留所有唯一的记录和每个组中的一个重复记录。

选择重复记录：

SELECT *
FROM <table>
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY <column1>, <column2>
);

删除重复项：

DELETE FROM <table>
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY <column1>, <column2>
);

注意处理大量记录时可能会导致性能问题。

- Martin Silovský

---

删除查询出错 - 无法在FROM子句中指定目标表'cities'进行更新。 - Ali Azhar

没有名为'cities'的表或更新子句。这是什么意思？删除查询中哪里出错了？ - Martin Silovský

Answer 9

试试这段代码

WITH CTE AS

( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE 

- Tanmay Nehete

Answer 10

如果你使用Oracle，这种方式会更可取：

create table my_users(id number, name varchar2(100), email varchar2(100));

insert into my_users values (1, 'John', '[e-mail]');
insert into my_users values (2, 'Sam', '[e-mail]');
insert into my_users values (3, 'Tom', '[e-mail]');
insert into my_users values (4, 'Bob', '[e-mail]');
insert into my_users values (5, 'Tom', '[e-mail]');

commit;

select *
  from my_users
 where rowid not in (select min(rowid) from my_users group by name, email);

- xDBA