在 SQL 表中查找重复值

落日映苍穹つ 2024-03-30 15:49 35阅读 0赞

## 问： ##

很容易找到一个字段的重复项：

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，因为它们都具有相同的 email。

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

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

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

## 答1: ##

> huntsbot.com汇聚了国内外优秀的初创产品创意，可按收入、分类等筛选，希望这些产品与实践经验能给您带来灵感。

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

只需在两列上进行分组。

注意：旧的 ANSI 标准是在 GROUP BY 中包含所有非聚合列，但这已经随着 “functional dependency” 的想法而改变：

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

支持不一致：

最近的 PostgreSQL 支持它。

SQL Server（与 SQL Server 2017 一样）仍然需要 GROUP BY 中的所有非聚合列。

MySQL 是不可预测的，你需要 sql\_mode=only\_full\_group\_by: GROUP BY lname ORDER BY 显示错误的结果；在没有 ANY() 的情况下，这是最便宜的聚合函数（请参阅已接受答案中的评论）。

GROUP BY lname ORDER BY 显示错误结果；

在没有 ANY() 的情况下，这是最便宜的聚合函数（请参阅已接受答案中的评论）。

Oracle 不够主流（警告：幽默，我不了解 Oracle）。

`@webXL WHERE 与单个记录一起使用 HAVING 与组一起使用`

`@gbn 是否可以在结果中包含 ID？那么之后删除这些重复项会更容易。`

`@user797717：如果 MIN(ID) 值，您需要拥有 MIN(ID) 然后删除最后一个 ID 值`

`任何列具有空值的情况怎么办？`

`非常感谢，是的，它确实在 Oracle 中工作，尽管我需要条件的唯一性，而不是 >1 =1`

## 答2: ##

> huntsbot.com聚合了超过10+全球外包任务平台的外包需求，寻找外包任务与机会变的简单与高效。

尝试这个：

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)

如果您想要 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.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)

`表名区分大小写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 ' } 附近使用正确的语法`

## 答3: ##

> huntsbot.com – 程序员副业首选，一站式外包任务、远程工作、创意产品分享订阅平台。

尝试这个：

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

> huntsbot.com高效搞钱，一站式跟进超10+任务平台外包需求

`完美的。谢谢！`

## 答4: ##

> 与HuntsBot一起，探索全球自由职业机会–huntsbot.com

如果要删除重复项，这是一种比在三重子选择中查找偶数/奇数行更简单的方法：

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
    )

更容易阅读和理解恕我直言

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

`美观且易于阅读；我想找到一种一次性删除多个重复行的方法。`

`这对我不起作用，因为我得到 You can't specify target table 'users' for update in FROM clause`

`@Whitecat 似乎是一个简单的 MySQL 问题：stackoverflow.com/questions/4429319/…`

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

`我认为 where 子句应该是“u.name = u2.name AND u.email = u2.email AND (u.id > u2.id OR u2.id > u.id)”不是吗？`

## 答5: ##

> huntsbot.com高效搞钱，一站式跟进超10+任务平台外包需求

与其他答案相比，您可以查看包含所有列（如果有）的 whole 记录。在 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 中测试。

`对 SELECT * 的细微改动帮助我解决了一个小时的搜索问题。我以前从未使用过 OVER(PARTITION BY。我对在 SQL 中有多少种方法可以做同样的事情感到惊讶！`

## 答6: ##

> HuntsBot周刊–不定时分享成功产品案例，学习他们如何成功建立自己的副业–huntsbot.com

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

## 答7: ##

> 保持自己快人一步,享受全网独家提供的一站式外包任务、远程工作、创意产品订阅服务–huntsbot.com

聚会有点晚了，但我找到了一个非常酷的解决方法来查找所有重复的 ID：

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

`似乎是一种语法糖变通。很好的发现。`

`请记住，GROUP_CONCAT 将在某个预定长度后停止，因此您可能无法获得所有的 id。`

## 答8: ##

> 一个优秀的自由职业者，应该有对需求敏感和精准需求捕获的能力，而huntsbot.com提供了这个机会

这会从每组重复项中选择/删除除一条记录之外的所有重复记录。因此，删除会留下所有唯一记录 + 每组重复项中的一条记录。

选择重复项：

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
    );

请注意大量记录，这可能会导致性能问题。

`删除查询出错 - 您无法在 FROM 子句中指定目标表“城市”进行更新`

`既没有表“城市”也没有更新子句。你是什么意思？删除查询中的错误在哪里？`

## 答9: ##

> 一个优秀的自由职业者，应该有对需求敏感和精准需求捕获的能力，而huntsbot.com提供了这个机会

试试这个代码

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

## 答10: ##

> huntsbot.com汇聚了国内外优秀的初创产品创意，可按收入、分类等筛选，希望这些产品与实践经验能给您带来灵感。

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

create table my_users(id number, name varchar2(100), email varchar2(100));
    
    insert into my_users values (1, 'John', 'asd@asd.com');
    insert into my_users values (2, 'Sam', 'asd@asd.com');
    insert into my_users values (3, 'Tom', 'asd@asd.com');
    insert into my_users values (4, 'Bob', 'bob@asd.com');
    insert into my_users values (5, 'Tom', 'asd@asd.com');
    
    commit;
    
    select *
      from my_users
     where rowid not in (select min(rowid) from my_users group by name, email);

## 答11: ##

> huntsbot.com精选全球7大洲远程工作机会，涵盖各领域，帮助想要远程工作的数字游民们能更精准、更高效的找到对方。

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

`Stack Overflow 不赞成仅代码的答案，您能解释一下为什么这会回答这个问题吗？`

`@RichBenner：我没有找到响应，例如结果中的每一行和每一行，它告诉我们哪些都是重复的行，哪些不是一目了然，不分组，因为如果我们想组合这个与任何其他查询分组查询不是一个好的选择。`

`将 Id 添加到 select 语句并过滤 duplicated ，它使您可以删除重复的 id 并保留每个。`

原文链接：[https://www.huntsbot.com/qa/8K7l/finding-duplicate-values-in-a-sql-table?lang=zh\_CN&from=csdn][https_www.huntsbot.com_qa_8K7l_finding-duplicate-values-in-a-sql-table_lang_zh_CN_from_csdn]

> huntsbot.com洞察每一个产品背后的需求与收益，从而捕获灵感

[https_www.huntsbot.com_qa_8K7l_finding-duplicate-values-in-a-sql-table_lang_zh_CN_from_csdn]: https://www.huntsbot.com/qa/8K7l/finding-duplicate-values-in-a-sql-table?lang=zh_CN&from=csdn