oracle删除一个表中的重复数据，且只保留一条

 创建表：

 1 -- Create table
 2 create table STUDENT
 3 (
 4   ID   INTEGER,
 5   语文 VARCHAR2(4000),
 6   数学 VARCHAR2(50),
 7   英语 VARCHAR2(50)
 8 )
 9 tablespace USERS
10   pctfree 10
11   initrans 1
12   maxtrans 255
13   storage
14   (
15     initial 64K
16     next 1M
17     minextents 1
18     maxextents unlimited
19   );

插入数据：

1 insert into student(id) values('1');
2 insert into student(id) values('1');
3 insert into student(id) values('1');
4 insert into student(id) values('1')

例子1：查找一个表中的重复数据，重复记录是根据单个字段（Id）来判断，表名为STUDENT

       SELECT ID ,
              COUNT(1)
         FROM STUDENT T
     GROUP BY T.ID HAVING COUNT(ID) > 1

 

例子2：删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录，表名为STUDENT

DELETE FROM STUDENT
      WHERE ID    IN     (SELECT ID 
                            FROM STUDENT T
                        GROUP BY T.ID HAVING COUNT(ID) > 1)
        AND ROWID NOT IN (SELECT MIN(ROWID)
                            FROM STUDENT T
                        GROUP BY T.ID
                          HAVING COUNT(T.ID) > 1)

 

posted on 2016-08-04 18:12  vbvb520  阅读(352)  评论(0)  编辑  收藏  举报

导航