当数据库表里有重复记录时怎么删除重复的那些数据呢?
当然前提是重复的定义:在数据库表,我们认为某个或者某几个字段相同,那就认为这条记录重复了。
如下表:
ROWID | DEP_ID | DEP_NAME | COMPANY |
---|---|---|---|
AAARJ4AAEAAABeGAAA | 13 | 人事部 | 联想二部 |
AAARJ4AAEAAABeIAAA | 11 | 研发部 | 百度hahahaha |
AAARJ4AAEAAABeIAAB | 12 | 人事部 | 联想一部 |
在这张表中,我认为dep_name重复的记录就是重复的。我想删除多余的人事部,只保留一个人事部。
sql如下:
select d.rowid from LSY_DEPARTMENT d /* delete from LSY_DEPARTMENT d*/
where d.rowid in
(
select rowid from LSY_DEPARTMENT c
where c.dep_name in
(
select a.dep_name from LSY_DEPARTMENT a
where 1=1
group by dep_name
having count(a.dep_name)>1
)
) and d.rowid not in
(
select min(t.rowid) from LSY_DEPARTMENT t
where 1=1
group by t.dep_name
having count(*)>1
)
ROWID |
---|
AAARJ4AAEAAABeIAAB |
把上面第一行改成delete即可。