Oracle 查询表中重复记录

--查询表中的重复记录

--方法一
select c.*
  from dchngrouprolegrant c
 where exists (select 1
          from (select max(a.rowid) as row_id, a.group_id
                  from dchngrouprolegrant a
                 where exists (select 1
                          from dchngrouprolegrant b
                         where a.rowid <> b.rowid
                           and a.group_id = b.group_id)
                 group by a.group_id) t
         where c.group_id = t.group_id
           and c.rowid <> t.row_id);

--方法二
declare
begin
  for rec in (select a.group_id as group_id, count(a.group_id) as cn
                from dchngrouprolegrant a
               group by a.group_id
              having count(a.group_id) > 1) loop
    delete from dchngrouprolegrant a
     where a.group_id = rec.group_id
       and rownum < rec.cn;
    commit;
  end loop;
  commit;
end;

--方法三--分析函数
select a.rowid, a.*
  from dChnToCUser a
 where a.rowid in (select rid
                     from (select rowid rid,
                                  row_number() over(partition by subs_id order by rn asc) rn
                             from dChnToCUser)
                    where rn <> 1)
--方法四--伪列自关联
select a.rowid, a.*
  from dChnToCUser a
 where a.rowid <
       (select max(rowid) from dChnToCUser where subs_id = a.subs_id);