表结构如下:
方法一(此方法需表包含唯一标识字段):
DELETE
FROM
PRINCIPAL
WHERE
ID NOT IN (
SELECT
*
FROM
(
SELECT
ID
FROM
PRINCIPAL
GROUP BY
NAME,
AGE,
EMAIL
) AS T
)
方法二(此方法需表包含唯一标识字段):
DELETE
FROM
PRINCIPAL
WHERE
ID IN (
SELECT
T.ID
FROM
(
SELECT
A.ID
FROM
PRINCIPAL A
LEFT JOIN (
SELECT
ID
FROM
PRINCIPAL
GROUP BY
NAME,
AGE,
EMAIL
) B ON A.ID = B.ID
WHERE
B.ID IS NULL
) AS T
)
方法三(此方法需表包含唯一标识字段):
DELETE
FROM
principal
WHERE
id IN (
SELECT
t.id
FROM
(
SELECT
a.id
FROM
principal a
WHERE
a.id > (
SELECT
min(id)
FROM
principal b
WHERE
b. NAME = a. NAME
)
) AS t
)
在表字段没有唯一标识时需要借助临时表,当然在Oracle中可借用rowid充当方法一、方法二的唯一标识来完成删除操作:
方法四(此方法无需表包含唯一标识字段):
/* 将过滤的数据放入临时表*/
select distinct *
into #temp
from principal
/*删除原表的数据*/
delete from principal
/*将甩选的数据插入原表*/
insert
principal
select *
from #temp
/*删除临时表*/
drop table #temp
select * from principal
注意:
1.仔细看你可能怀疑方法一、方法二中多用了一层select,不过本人亲试过不加这一层select在MySQL中会报错(You can't specify target table 'PRINCIPAL' for update in FROM clause),在Oracle中应该没问题;
2.在网上可看到很多类似下面的删除重复的sql语句,但是自己看会发现逻辑是错误的
DELETE
FROM
principal
WHERE
id IN (
SELECT
id
FROM
(
SELECT
id,
NAME,
age,
email
FROM
principal
GROUP BY
NAME,
age,
email
HAVING
count(*) > 1
) t
)
/*这样的删除语句每次只能删除重复记录中的一条*/