1 无主键表-使用临时表
整体思路是先找出重复的记录放入临时表中,然后删除原表中的重复数据,再将临时表中的数据插入进去
找出重复的放入临时表:
create table tmp_table as (select name,age,sex, count(1) as cn from ori_table group by name, age,sex having cn > 1);
删除原表重复的:
delete ori_table from ori_table inner join tmp_table using (name,age,sex);
插入重复数据:
insert into ori_table select name,age,sex from tmp_table;
删除临时表:
drop table tmp_table;
2 有主键表-使用id剔除
select count(1) from
# 这个是实际的删除sql,大概思路就是按照id删除,但是剔除最小的那个id
delete from
test_table where id in
(
SELECT id from
(
select id from test_table as t1,
(
SELECT p_id,s_id from test_table group by p_id,s_id having COUNT(*) > 1
) as t2
where t1.p_id = t2.p_id and t1.s_id = t2.s_id
# 如果存在为空的情况,还要用isNull
#where t1.p_id is NULL and t1.s_id = t2.s_id
) as t3
)
and id not in
(
SELECT id from
(
SELECT min(id) as id FROM test_table GROUP BY p_id,s_id HAVING count(1) > 1
) as t4
)
# 计算要删除的数据条目数
SELECT
(
SELECT SUM(cn) as ct from (
SELECT COUNT(*) as cn , p_id,s_id from test_table group by p_id,s_id having cn > 1
) as t2
) -
(
SELECT COUNT(id) from
(
SELECT min(id) as id FROM test_table GROUP BY p_id,s_id HAVING count(1) > 1
) as t4
) as diffNum
3 使用联表删除:全删除,不保留数据
DELETE test_table from test_table,
(
SELECT COUNT(*) as cn , o_id,s_id from test_table group by o_id,s_id having cn > 1
) as t2
WHERE test_table.o_id=t2.o_id and test_table.s_id=t2.s_id;
# null也要做特殊处理
WHERE test_table.o_id=t2.o_id and test_table.s_id is null;
PS:
【JAVA核心知识】系列导航 [持续更新中…]
欢迎关注…