场景:
删除这三列重复的数据只保留一份
实现:
delete from ehr.PH_MENU_ORG a
where (a.ORG_ID,a.MENU_ID,a.PRO_TYPE) in
(select ORG_ID,MENU_ID,PRO_TYPE
from ehr.PH_MENU_ORG
where PRO_TYPE = 5
and org_Id = '243057cf-10fe-434d-a0bb-0c031131e885'
group by ORG_ID,MENU_ID,PRO_TYPE
having count(*) > 1)
and rowid not in (select min(rowid)
from ehr.PH_MENU_ORG
where PRO_TYPE = 5
and org_Id = '243057cf-10fe-434d-a0bb-0c031131e885'
group by ORG_ID,MENU_ID,PRO_TYPE
having count(*) > 1)
执行结果:
重复的四条数据已经成功删除
总结
delete from 表 a
where (a.Id, a.seq) in
(select Id, seq from 表 group by Id, seq having count(*) > 1)
and rowid not in
(select min(rowid) from 表 group by Id, seq having count(*) > 1)