【小笔记】临时表：删除无主键表中的重复记录，只保留一条记录

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核心知识】系列导航 [持续更新中…]
欢迎关注…