--查询及删除重复记录的SQL语句
--1、查找wolf.WB_DEPT中多余的重复记录,重复记录是根据单个字段(DEPT_ID)来判断 ;
select *
from wolf.WB_DEPT
where DEPT_ID in (select DEPT_ID
from wolf.WB_DEPT
group by DEPT_ID
having count(DEPT_ID) > 1)
--2、删除wolf.WB_DEPT中多余的重复记录,重复记录是根据单个字段(DEPT_ID)来判断,只留有rowDEPT_ID最小的记录;
DELETE from wolf.WB_DEPT
WHERE (DEPT_ID) IN (SELECT DEPT_ID
FROM wolf.WB_DEPT
GROUP BY DEPT_ID
HAVING COUNT(DEPT_ID) > 1)
AND ROWID NOT IN (SELECT MIN(ROWID)
FROM wolf.WB_DEPT
GROUP BY DEPT_ID