如何从这些副本中净化我的数据库？

Question

我有一个具有以下字段的表: id _ domainname \\ domain_certificate_no _

select语句的输出示例如下：

'57092', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_1', '55525772666'
'57093', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_2', '22225554186'
'57094', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_3', '22444356259'
'97168', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_1', '55525772666'
'97169', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_2', '22225554186'
'97170', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_3', '22444356259’

我需要清理我的数据库，以便:我想删除第一个keyvalue的重复domain_certificate_no的域名(即，在本例中，我查找字段domain_certificate_no：02aa6aa.netsolstores.com_1，因为它是1，并且密钥具有重复值)，然后我想删除整个链，即02aa6aa.netsolstores.com_2和02aa6aa.netsolstores.com_3，并删除这个链所属的域名02aa6aa.netsolstores.com。

如何使整个DB的检查过程自动化。因此，我有一个查询，它检查模式('%.%.%) 编辑：中的任何域名，并且它们有共享的域名(在这个ex：netsolstores.com中)，如果它找到属于这个域名的cert no. 1有一个重复的键值，那么删除。否则就不会了。请注意，请注意，如果domain_certificate_no不是数字1，那么它可以有重复值。

编辑：i只比较同一二级域名的重复值。例:在这个问题中，我比较了共享域名的值：.netsolstores.com。如果我有另一个域名，有子级别的域名，我也是这样做的。但重点是我不需要比较整个DB。只有具有共享域名的值(但不同的子域)。

Answer 1

我不知道在你的例子中“02aa6aa.netsolstores.com_1”会发生什么。

以下仅保留任何重复键的最小id：

with t as (
     select t.*,
            substr(domain_certificate_no,
                   instr(domain_certificate_no, '_') + 1, 1000) as version,
            left(domain_certificate_no, instr(domain_certificate_no, '_') - 1) as dcn
     from t
    )
select t.*
from t join
     (select keyvalue, min(dcn) as mindcn
      from t
      group by keyvalue
     ) tsum
     on t.keyvalue = tsum.keyvalue and
        t.dcn = tsum.mindcn 

对于您提供的数据，这似乎是可行的。这将不会返回重复的"_1“版本。如果这一点很重要，那么查询就可以很容易地修改。

虽然我更喜欢积极一点(考虑要保留的行，而不是删除行)，但以下内容应该删除您想要的内容：

with t as (
     select t.*,
            substr(domain_certificate_no,
                   instr(domain_certificate_no, '_') + 1, 1000) as version,
            left(domain_certificate_no, instr(domain_certificate_no, '_') - 1) as dcn
     from t
    ),
    tokeep as (
     select t.*
     from t join
          (select keyvalue, min(dcn) as mindcn
           from t
           group by keyvalue
          ) tsum
          on t.keyvalue = tsum.keyvalue and
             t.dcn = tsum.mindcn
    )
delete from t
where t.id not in (select id from tokeep)

还有其他更有效的表达方法(取决于数据库)。不过，这保留了原始查询的结构。

顺便说一下，在尝试新的删除代码时，请确保您保存了表的副本。删除(和更新)很容易出错。例如，如果省略WHERE子句，在记录所有这些行的漫长痛苦过程之后，所有行都将消失。您可能会发现，简单地将所需的结果选择到一个新表中，验证它们，然后截断旧表并重新插入它们会更快。