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
- )
联系客服