假设订单表tbl_order存在重复的记录:
一.查找表中存在重复的记录
1.单个字段存在重复的情况
select * from tbl_order t where t.order_no in (select t.order_no from tbl_order t group by t.order_no having count(*) > 1)
结果:
1 001 订单1
2 002 订单2
3 002 订单2
4 001 订单1
2.多个字段存在重复的情况:
select * from tbl_order t where (t.order_no,t.order_desc) in (select t.order_no,t.order_desc from tbl_order t group by t.order_no,t.order_desc having count(*) > 1)
1.子查询方式
delete from tbl_order t
where (t.order_no,t.order_desc) in (select t.order_no,t.order_desc from tbl_order t group by t.order_no,t.order_desc having count(*) > 1)
and ROWID not in (select MIN(ROWID) from tbl_order t group by t.order_no,t.order_desc having count(*) > 1)
结果:
1 001 订单1
2 002 订单2
5 003 订单3
解释:第一个子查询查询出所有重复的记录,第二个子查询查出所有重复记录中最小的ROWID的记录,整个语句的作用是删除多条重复记录但保留一条ROWID最小的。
2.临时表方式
create table tmp as select distinct * from tbl_order;
truncate table tbl_order;
insert into tbl_order select * from tmp;