删除数据库的数据为安全起见，先备份表
begin tran
SELECT * INTO tablename_backup
FROM tablename
rollback
先查重重复数据
select Sp_no
from tablename
group by Sp_no
having count(Sp_no)>1
查询需要保留的数据
SELECT Sp_no, Oid = MIN(Oid)
FROM tablename
GROUP BY Sp_no
having count(Sp_no)>1
查询需要删除的数据
select *
from tablename
where Spno in (select Spno
from tablename
group by Sp_no
having count(Sp_no)>1)
and
Oid not in (
SELECT Oid = MIN(Oid)
FROM tablename
GROUP BY
Sp_no
having count(Sp_no)>1
)
删除多余数据
delete from tablename
where Oid in (
select Oid
from tablename
where Spno in (select Spno
from tablename
group by Sp_no
having count(Sp_no)>1)
and
Oid not in (
SELECT Oid = MIN(Oid)
FROM tablename
GROUP BY
Sp_no
having count(Sp_no)>1
)
)
