我有数据库,其中有电话号码,orgno,公司名称,邮政编码等数据。
我的数据库中也有一些重复项,例如相同的公司名称,相同的组织编号,相同的邮政编码,但不同的相同电话号码。所以我想使用脚本删除这些重复项。我怎样才能写出这样的脚本?
CREATE TABLE TEST (
address_id bigint,
name varchar(255),
marketing_name varchar(255),
co_address varchar(255),
box_address1 varchar(255),
box_address2 varchar(255),
box_postal_code varchar(45),
visit_address1 varchar(255),
org_nr varchar(255),
visit_postal_code varchar(255),
county_id varchar(255),
contact varchar(255),
complete tinyint,
deleted tinyint,
district_id varchar(255),
region varchar(255),
phone1 varchar(255),
mobile_phone varchar(255),
)
首先,您需要确定将使用的重复标准是什么。例如,我想检查电话号码是否在同一组织Nro,公司名称和邮政编码中重复。我可以这样做:
第一个测试表
CREATE TABLE TEST
(
id int,
phonenumber int,
orgno int,
companyname varchar(100),
postalcode 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,
postalcode,
COUNT(*)
from test
group by phonenumber,
orgno,
companyname,
postalcode
HAVING COUNT(*) > 1
使用此查询,您可以轻松找到重复项,最重要的是查看重复项的条件。
编辑
如果要删除除 1 之外的所有重复行,可以执行以下操作:
DELETE A
FROM TEST A
INNER JOIN
(
SELECT ROW_NUMBER()OVER(PARTITION BY a.phonenumber,
a.orgno,
a.companyname,
a.postalcode ORDER BY a.id)AS POS,
a.phonenumber,
a.orgno,
a.companyname,
a.postalcode,
a.id
FROM TEST A
JOIN
(
SELECT
phonenumber,
orgno,
companyname,
postalcode,
COUNT(*) AS CONTADOR
FROM test
GROUP BY phonenumber,
orgno,
companyname,
postalcode
HAVING COUNT(*) > 1
) TB
ON A.companyname = TB.companyname
AND A.orgno = TB.orgno
AND A.phonenumber = TB.phonenumber
AND A.postalcode = TB.postalcode
) TB_2
ON
a.id = tb_2.id
and tb_2.pos > 1
编辑,使用提供的数据结构编写脚本。
DELETE FROM TEST B
JOIN
(
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_2
ON
b.address_id = tb_2.address_id
and tb_2.pos > 1