例子:
有这么个表
SQL> select * from t1;
ID V_ID
1 11
2 22
3 33
1 11
2 22
3 33
4 43
—-查询有重复行的ID
SQL> select id,count() from t1 group by id having count()>1;
ID COUNT(*)
1 2
2 2
3 2
—–显示存在重复行的所有数据
SQL> select a.* from t1 a,(select id,count() from t1 group by id having count()>1) b where a.id=b.id;
ID V_ID
1 11
1 11
2 22
2 22
3 33
3 33
—-删除重复数据,根据单个字段id判断,只保留rowid 最大的行
SQL >delete from t1 where id in (select id from t1 group by id having count()>1) and rowid not in (select max(rowid) from t1 group by id having count()>1);
SQL >select * from t1;
ID V_ID
1 11
3 33
2 22
4 43