利用伪列(rowid)删除重复记录,并保留一条记录。

  • A+
所属分类:SQL笔记

在oracle中,伪列rowid是不显示的,表中每条记录都有一个唯一的rowid,如果想保留最新(MAX)或最早(MIN)记录,可以利用这个列。

方法一:

  1. 利用伪列创建一张带临时表,将需要判断重复的列,rowid插入到临时表:
  1. CREATE TABLE 临时表 AS
  2.     SELECT a.列1,a.列2,MAX(a.ROWID) AS maxid    --最新记录用MAX,最早记录用MIN
  3.     FRIM 正式表 a
  4.     GROUP BY a.列1,a.列2;

2. 利用正式表rowid、临时表的maxid进行比较,两者不相等的进行删除,保留唯一记录:

  1. DELETE
  2. FROM  正式表 a
  3. WHERE a.rowid !=        --正式表的rowid不等于临时表的maxid
  4. (
  5.    SELECT b.maxid       --取得单行单列的maxid
  6.    FROM 临时表 b
  7.    WHERE   a.列1 = b.列1
  8.          AND a.列2 = b.列2
  9. );

方法二:

获得重复列分组中rowid最小的那条记录的rowid,然后把这条记录删除,如果两行以上记录重复,可以先删除表中重复的一部分数据,再次执行相同语句分多次删除重复数据。

  1. DELETE
  2. FROM 表
  3. WHERE rowid IN
  4. -- 子查询:通过[列名1]进行分组,提取[列名1]重复的记录中rowid值最后小的记录 
  5. (
  6.     SELECT MIN(rowid)
  7.     FROM 表
  8.     GROUP BY by 列名1
  9.     HAVING COUNT(*) > 1
  10. );

如果记录重复的行太多, 该SQL语句就需要执行N次,不想重复执行可编写存储过程,重复执行该SQL语句,直到受影响的行为0,创建存储过程:

  1. create or replace procedure deletetemp
  2. as
  3. begin
  4.   loop
  5.     delete from 表 where rowid in (select min(rowid) from 表 group by 列1 having count(*) > 1);
  6.     dbms_output.put_line('删除了 '||to_char(sql%rowcount)||' 行记录');
  7.     exit when sql%rowcount = 0;
  8.   end loop;
  9. end;
  10. /

执行存储过程(SQL*PLUS下执行):

  1. EXEC deletetemp();

 

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: