删除重复记录

select
commend_date, commender_id, stock_code,stock_name,issue_channel
from commendstock a
where (a.commend_date, a.commender_id, a.stock_code)
in (select commend_date, commender_id, stock_code
from commendstock group by commend_date, commender_id, stock_code
having count(*)>1
)
--and rowid not in(select min(rowid) from commendstock 
-- group by commend_date, commender_id, stock_code
--having count(*)>1)
order by commend_date,commender_id,stock_code

;

 

现在要删除表中重复的记录。条件是：3个字段重复，同时比较第4个字段的值，保留值比较小的那一条
例如：

字段1 字段2 字段3 字段4
数值1 数值2 数值3 1
数值1 数值2 数值3 4

因为前3个字段都重复，第一条记录的字段4值为1，比第二条记录的字段4的值小，所以删除第二条，保留第一条。

select * from commendstock t
where exists(select 1 from commendstock t2
             where t.commend_date=t2.commend_date
               and t.commender_id=t2.commender_id
               and t.stock_code=t2.stock_code
               and t.commend_type>t2.commend_type)