sql 去重 查找并删除重复记录并且只留一条记录

1.支持单条件或者多条件，查找重复记录，语句：

select * from stock a  
where (a.state,a.org_id,a.material_id) 
in  (select state,org_id,material_id from stock 
where state = 1 group by state,org_id ,material_id  having count(*) > 1)  

    其中“a.state”、“a.org_id”、“material_id”,为条件查询，也就是这三个调件相同。

2.删除重复记录并且只留一条记录

delete from stock   
where (org_id,material_id,state) 
in  (SELECT * from 
(select org_id,material_id, state from stock WHERE state = 1 group by org_id,material_id,state having count(*) > 1)
 a) 
and stock_id 
not in (SELECT * from
(select min(stock_id) from stock where state = 1 group by org_id,material_id,state having count(*)>1) b
)