关于数据库多条件删除重复数据解决思路
关于数据库多条件删除重复数据
delete from Detection_info a
where (a.Cph,a.cpys) in (select Cph,Cpys from Detection_info group by Cph,Cpys having count(*) > 1)
and Jcrq not in (select min(Jcrq) from vitae group by Cph,Cpys having count(*)>1)
------解决方案--------------------
多条件用exists
------解决方案--------------------
mssql 的delete语句和Oracle语句在使用别名时候是有区别的
delete from Detection_info a
where (a.Cph,a.cpys) in (select Cph,Cpys from Detection_info group by Cph,Cpys having count(*) > 1)
and Jcrq not in (select min(Jcrq) from vitae group by Cph,Cpys having count(*)>1)
------解决方案--------------------
多条件用exists
DELETE Detection_info
FROM Detection_info
WHERE EXISTS ( SELECT 1
FROM ( SELECT Cph ,
Cpys
FROM Detection_info
GROUP BY Cph ,
Cpys
HAVING COUNT(*) > 1
) b
WHERE Detection_info.cph = b.cph
AND Detection_info.cpys = b.cpys )
AND Jcrq NOT IN ( SELECT MIN(Jcrq)
FROM vitae
GROUP BY Cph ,
Cpys
HAVING COUNT(*) > 1 )
------解决方案--------------------
delete Detection_info from Detection_info a
JOIN (select Cph,Cpys from Detection_info group by Cph,Cpys having count(*) > 1) AS B ON a.Cph=B.Cph AND a.cpys=B.cpys
where Jcrq not in (select min(Jcrq) from vitae group by Cph,Cpys having count(*)>1)
mssql 的delete语句和Oracle语句在使用别名时候是有区别的