Oracle数据库中由于没有做任何约束,如何删掉表中重复的记录


Oracle数据库中由于没有设置任何约束，里面含有很多的重复项。现在的问题是如何删掉这些重复项，重复的记录只保留其中的一条。如下建表语句

CREATE TABLE message_student3(

      stu_id INTEGER NOT NULL,  --表中没有唯一性约束

      stu_number varchar(30),

      stu_name VARCHAR(10) NOT NULL,

      stu_age NUMBER(2) NOT NULL

);

INSERT INTO message_student3 VALUES(1,'100320036','张珊',18);

INSERT INTO message_student3 VALUES(2,'100320037','刘备',19);

INSERT INTO message_student3 VALUES(3,'100320038','张飞',19);

INSERT INTO message_student3 VALUES(4,'100320039','吕布',19);

INSERT INTO message_student3 VALUES(5,'100320040','周瑜',19);

INSERT INTO message_student3 VALUES(6,'100320041','孙权',19);

INSERT INTO message_student3 VALUES(7,'100320042','张辽',19);

INSERT INTO message_student3 VALUES(1,'100320036','张珊',18);

INSERT INTO message_student3 VALUES(2,'100320037','刘备',19);

INSERT INTO message_student3 VALUES(3,'100320038','张飞',19);

INSERT INTO message_student3 VALUES(4,'100320039','吕布',19);

INSERT INTO message_student3 VALUES(5,'100320040','周瑜',19);

INSERT INTO message_student3 VALUES(6,'100320041','孙权',19);

INSERT INTO message_student3 VALUES(7,'100320042','张辽',19);

分析：由于表没有做约束，所以重复记录的各个字段必然是相等的，然而对于删除操作要么是全部删除要么是根据一个条件删除，前者肯定是不行，后者呢，一删除就重复的都删除了。所以要删掉其中重复项的切入点必须要有一个可以区别不同记录的量。想想在做oracle的分页的时候，用到了rownum，于是想用rownum来试一下。但是每一个新的select语句都有一个新的rownum，用别名就是无法之前的，这么写可能有些含糊，于是在网上找了一些有关这方面的资料，发现其实我应该要用的是rowid。rownum和rowid有什么不同呢？下面是摘抄的内容：原地址是：

rownum和rowid都是伪列，但是两者的根本是不同的，rownum是根据sql查询出的结果给每行分配一个逻辑编号，所以你的sql不同也就会导致最终rownum不同，

但是rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录 （不会变），
例如  AAAMgzAAEAAAAAgAAB 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
这里的AAAMgzAAEAAAAAgAAB物理位置对应了这条记录，这个记录是不会随着sql的改变而改变。
因此，这就导致了他们的使用场景不同了，通常在sql分页时或是查找某一范围内的记录时，我们会使用rownum。

1、rownum
例如：
查找2到10范围内的记录（这里包括2和10的记录）

select *

  from (select rownum rn, a.* from emp a) t

where t.rn between 2 and 10;

查找前三名的记录
select * from emp a where rownum < 3；这里我们要注意，直接用rownum查找的范围必须要包含1；因为rownum是从1开始记录的，当然你可以把rownum查出来后放在一个虚表中作为这个虚表的字段再根据条件查询。
例如：

select *

  from (select rownum rn, a.* from emp a) t

where t.rn > 2;

2、rowid
我们在处理一张表中重复记录时经常用到他
因此我们可以根据rowid来处理，rowid具有唯一性，查询时效率是很高的，
回到最开始的问题，要删掉其中重复的使用下面的语句就可了

delete from message_student3 a where rowid > (select min(rowid) from  message_student3 b where a.stu_id = b.stu_id);

commit;


这样就可以了。

当然你也可以用一个很原始的方法，就是将有重复记录的表中的数据导到另外一张表中，最后再倒回去。

create table stu_tmp as select distinct* from stu;

truncate table sut;        //清空表记录

insert into stu select * from stu_tmp;    //将临时表中的数据添加回原表但是要是stu的表


这种只适合数量级不大的情况下。

相关内容