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
)