查询重复记录的语句 select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd) 这个你可能能用着
delete from a where rowid!=(select max(rowid) from a b where a.yhbh =b.yhbh and a.bgxm=b.bgxm and.......);
rowid一定不同,可以用delete from ... where rowid = 其实用rownum也是可以的.如果两条记录重复,可以用 delete from ... where rownum=1 不过这样的方法比较老土.
删除重复列的方法(1) DELETE FROM table_name A WHERE ROWID > ( SELECT min(rowid) FROM table_name B WHERE A.key_values = B.key_values); (2) create table table2 as select distinct * from table1; drop table1; rename table2 to table1; (3) Delete from mytable where rowid not in( select max(rowid) from mytable group by column_name ); (4) delete from mytable t1 where exists (select 'x' from my_table t2 where t2.key_value1 = t1.key_value1 and t2.key_value2 = t1.key_value2 ... and t2.rowid > t1.rowid);
delete from ... where rownum=1
luckysxn(风花雪) 个人认为他的方案不错---全,hehehehe
luckysxn(风花雪) 大侠讲得很清楚啦: (1) DELETE FROM table_name A WHERE ROWID > ( SELECT min(rowid) FROM table_name B WHERE A.key_values = B.key_values); (2) create table table2 as select distinct * from table1; drop table1; rename table2 to table1; (3) Delete from mytable where rowid not in( select max(rowid) from mytable group by column_name );
delete from table_name a where rowid< (select max(rowid) from table_name where column1=a.column1 and column2=a.column2 and colum3=a.colum3 and ...);
delete from table_name where 主键 in (select a.主键 from table_name a,table_name b where a.主键=b.主键 and a.rowid <>b.rowid)
其实这种情况完全用不到那么麻烦!只要这样一条命令就可以了,保证安全,不会有任何危险!!!create table another_table as select distinct * from table_name;这样another_table就是删除了重复记录的表!这个问题是解决了,不过我得提醒你,最好在你的表上建一个主键,以此来杜绝下次再出现类似问题。要知道,管理软件最怕业务记录出现问题,可能开始编写一个程序比简单,但是如果数据出现了毛病,那么后期的维护工作完全可以把你拖垮!!!
liu7537(一飞) 你的方法也有问题的你这样只能删除所有列都相同的,而实际上重复数据指的是 本应作为主键的字段有重复数据,其他字段则不考虑比如: 表a ___________ id1 number id2 number value varchar2(10)本来的主键应该为(id1,id2) 但现在里面有这样的数据 id1 id2 value -------------------- 1 1 a 1 1 b 这样根据你的方法就不能删除了解决办法:delete from a b where rowid != (select max(rowid) from a c where id1 = b.id1 and id2 = b.id2);
select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd) 这个你可能能用着
其实用rownum也是可以的.如果两条记录重复,可以用
delete from ... where rownum=1
不过这样的方法比较老土.
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2) create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
(4) delete from mytable t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
...
and t2.rowid > t1.rowid);
个人认为他的方案不错---全,hehehehe
(1) DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2) create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
where 主键 in
(select a.主键 from table_name a,table_name b
where a.主键=b.主键
and a.rowid <>b.rowid)
你的方法也有问题的你这样只能删除所有列都相同的,而实际上重复数据指的是 本应作为主键的字段有重复数据,其他字段则不考虑比如:
表a
___________
id1 number
id2 number
value varchar2(10)本来的主键应该为(id1,id2)
但现在里面有这样的数据
id1 id2 value
--------------------
1 1 a
1 1 b
这样根据你的方法就不能删除了解决办法:delete from a b
where rowid != (select max(rowid)
from a c
where id1 = b.id1
and id2 = b.id2);