1. <small id='kRs1i'></small><noframes id='kRs1i'>

        <bdo id='kRs1i'></bdo><ul id='kRs1i'></ul>
      <i id='kRs1i'><tr id='kRs1i'><dt id='kRs1i'><q id='kRs1i'><span id='kRs1i'><b id='kRs1i'><form id='kRs1i'><ins id='kRs1i'></ins><ul id='kRs1i'></ul><sub id='kRs1i'></sub></form><legend id='kRs1i'></legend><bdo id='kRs1i'><pre id='kRs1i'><center id='kRs1i'></center></pre></bdo></b><th id='kRs1i'></th></span></q></dt></tr></i><div id='kRs1i'><tfoot id='kRs1i'></tfoot><dl id='kRs1i'><fieldset id='kRs1i'></fieldset></dl></div>
    2. <legend id='kRs1i'><style id='kRs1i'><dir id='kRs1i'><q id='kRs1i'></q></dir></style></legend><tfoot id='kRs1i'></tfoot>
    3. MySQL删除重复记录但保持最新

      时间:2023-05-24

      1. <legend id='G1nES'><style id='G1nES'><dir id='G1nES'><q id='G1nES'></q></dir></style></legend>
          <tbody id='G1nES'></tbody>
        <tfoot id='G1nES'></tfoot>

            <i id='G1nES'><tr id='G1nES'><dt id='G1nES'><q id='G1nES'><span id='G1nES'><b id='G1nES'><form id='G1nES'><ins id='G1nES'></ins><ul id='G1nES'></ul><sub id='G1nES'></sub></form><legend id='G1nES'></legend><bdo id='G1nES'><pre id='G1nES'><center id='G1nES'></center></pre></bdo></b><th id='G1nES'></th></span></q></dt></tr></i><div id='G1nES'><tfoot id='G1nES'></tfoot><dl id='G1nES'><fieldset id='G1nES'></fieldset></dl></div>
              <bdo id='G1nES'></bdo><ul id='G1nES'></ul>
            • <small id='G1nES'></small><noframes id='G1nES'>

              • 本文介绍了MySQL删除重复记录但保持最新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                限时送ChatGPT账号..

                我有唯一的 idemail 字段.电子邮件被复制.我只想保留所有重复项的一个电子邮件地址,但使用最新的 id(最后插入的记录).

                I have unique id and email fields. Emails get duplicated. I only want to keep one Email address of all the duplicates but with the latest id (the last inserted record).

                我怎样才能做到这一点?

                How can I achieve this?

                推荐答案

                想象你的表 test 包含以下数据:

                Imagine your table test contains the following data:

                  select id, email
                    from test;
                
                ID                     EMAIL                
                ---------------------- -------------------- 
                1                      aaa                  
                2                      bbb                  
                3                      ccc                  
                4                      bbb                  
                5                      ddd                  
                6                      eee                  
                7                      aaa                  
                8                      aaa                  
                9                      eee 
                

                所以,我们需要找到所有重复的邮件并删除所有邮件,但要删除最新的 id.
                在这种情况下,aaabbbeee 是重复的,所以我们要删除 ID 1、7、2 和 6.

                So, we need to find all repeated emails and delete all of them, but the latest id.
                In this case, aaa, bbb and eee are repeated, so we want to delete IDs 1, 7, 2 and 6.

                要做到这一点,首先我们需要找到所有重复的电子邮件:

                To accomplish this, first we need to find all the repeated emails:

                      select email 
                        from test
                       group by email
                      having count(*) > 1;
                
                EMAIL                
                -------------------- 
                aaa                  
                bbb                  
                eee  
                

                然后,从这个数据集中,我们需要为这些重复的电子邮件中的每一个找到最新的 id:

                Then, from this dataset, we need to find the latest id for each one of these repeated emails:

                  select max(id) as lastId, email
                    from test
                   where email in (
                              select email 
                                from test
                               group by email
                              having count(*) > 1
                       )
                   group by email;
                
                LASTID                 EMAIL                
                ---------------------- -------------------- 
                8                      aaa                  
                4                      bbb                  
                9                      eee                                 
                

                最后,我们现在可以删除所有这些 Id 小于 LASTID 的电子邮件.所以解决方案是:

                Finally we can now delete all of these emails with an Id smaller than LASTID. So the solution is:

                delete test
                  from test
                 inner join (
                  select max(id) as lastId, email
                    from test
                   where email in (
                              select email 
                                from test
                               group by email
                              having count(*) > 1
                       )
                   group by email
                ) duplic on duplic.email = test.email
                 where test.id < duplic.lastId;
                

                我现在没有在这台机器上安装 mySql,但应该可以工作

                I don't have mySql installed on this machine right now, but should work

                上面的删除有效,但我发现了一个更优化的版本:

                The above delete works, but I found a more optimized version:

                 delete test
                   from test
                  inner join (
                     select max(id) as lastId, email
                       from test
                      group by email
                     having count(*) > 1) duplic on duplic.email = test.email
                  where test.id < duplic.lastId;
                

                您可以看到它删除了最旧的重复项,即 1、7、2、6:

                You can see that it deletes the oldest duplicates, i.e. 1, 7, 2, 6:

                select * from test;
                +----+-------+
                | id | email |
                +----+-------+
                |  3 | ccc   |
                |  4 | bbb   |
                |  5 | ddd   |
                |  8 | aaa   |
                |  9 | eee   |
                +----+-------+
                

                另一个版本,是由 Rene Limon

                delete from test
                 where id not in (
                    select max(id)
                      from test
                     group by email)
                

                这篇关于MySQL删除重复记录但保持最新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                上一篇:使用 LOAD DATA INFILE 导入 MySQL 表时如何跳过 CSV 文件中的列? 下一篇:如何在 LIMIT 子句中使用大偏移量加速 MySQL 查询?

                相关文章

                1. <small id='tUBA4'></small><noframes id='tUBA4'>

                  1. <i id='tUBA4'><tr id='tUBA4'><dt id='tUBA4'><q id='tUBA4'><span id='tUBA4'><b id='tUBA4'><form id='tUBA4'><ins id='tUBA4'></ins><ul id='tUBA4'></ul><sub id='tUBA4'></sub></form><legend id='tUBA4'></legend><bdo id='tUBA4'><pre id='tUBA4'><center id='tUBA4'></center></pre></bdo></b><th id='tUBA4'></th></span></q></dt></tr></i><div id='tUBA4'><tfoot id='tUBA4'></tfoot><dl id='tUBA4'><fieldset id='tUBA4'></fieldset></dl></div>

                  2. <tfoot id='tUBA4'></tfoot>
                  3. <legend id='tUBA4'><style id='tUBA4'><dir id='tUBA4'><q id='tUBA4'></q></dir></style></legend>

                      <bdo id='tUBA4'></bdo><ul id='tUBA4'></ul>