从数据库中删除重复项的脚本

首先，您需要确定将要使用的重复条件是什么。例如，我要检查电话号码是否在同一Org Nro，公司名称和邮政编码中重复。我可以做这个：

第一张表进行测试

CREATE TABLE TEST(id int,phonenumber int,orgno int,companyname varchar(100),postalpre varchar(4))

然后测试数据

  insert into TEST values (1,4533660,1,'COMPANY 1',1234)  insert into TEST values (2,4533660,1,'COMPANY 1',1234)  insert into TEST values (3,954189547,1,'COMPANY 2',4444)   insert into TEST values (4,954189547,1,'COMPANY 2',4444)   insert into TEST values (5,3652591,1,'COMPANY 3',4444)   insert into TEST values (6,4201580,1,'COMPANY 4',4444)   insert into TEST values (7,3337788,1,'COMPANY 5',4444)

最后，查询重复项，并选择条件。

 Select phonenumber, orgno, companyname, postalpre, COUNT(*) from test  group by phonenumber, orgno,  companyname, postalpre  HAVINg COUNT(*) > 1

通过此查询，您可以轻松找到重复项，最重要的是查看重复项的条件。

编辑

如果要删除除1之外的所有重复行，可以执行以下 *** 作：

DELETe AFROM TEST A INNER JOIN (        SELECt ROW_NUMBER()OVER(PARTITION BY a.phonenumber,        a.orgno,        a.companyname,        a.postalpre ORDER BY a.id)AS POS,        a.phonenumber,        a.orgno,        a.companyname,        a.postalpre,        a.id        FROM TEST A        JOIN         (     SELECt       phonenumber,     orgno,     companyname,     postalpre,     COUNT(*) AS ConTADOR     FROM test      GROUP BY phonenumber,     orgno,      companyname,     postalpre     HAVINg COUNT(*) > 1        ) TB        ON A.companyname = TB.companyname         AND A.orgno = TB.orgno         AND A.phonenumber = TB.phonenumber         AND A.postalpre = TB.postalpre) TB_2ONa.id = tb_2.id and tb_2.pos > 1

使用提供的数据结构进行编辑，编写脚本。

DELETe FROM TEST BJOIN (    SELECt ROW_NUMBER()OVER(PARTITION BY    a.org_nr,    a.phone1,    a.phone2,    a.name,    a.marketing_name    ORDER BY     a.org_nr,    a.phone1,    a.phone2,    a.name,    a.marketing_name    )AS POS,    a.address_id,    a.org_nr,    a.phone1,    a.phone2,    a.name,    a.marketing_name    FROM TEST A    JOIN     (        SELECt         org_nr,        phone1,        phone2,        name,        marketing_name        FROM TEST         GROUP BY         org_nr,        phone1,        phone2,        name,        marketing_name        HAVINg COUNT(*) > 1) TB        ON a.org_nr = tb.org_nr        and a.phone1 = tb.phone1        and a.phone2 = tb.phone2        and a.name = tb.name        and a.marketing_name = tb.marketing_name    ) TB_2ONb.address_id = tb_2.address_idand tb_2.pos > 1