`

oracle去重

 
阅读更多

我们在实际工作中,当信息系统数据质量不高的时候,可能存在数据表中有重复记录的问题。方法:

1、保留重复记录中的一条

delete from t where   rowid   not   in   (select   min(rowid)   from   t   group   by   去重字段);       not in  可使用!=

2、select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1
        将上面的>号改为=号就可以查询出没有重复的数据了。

3、要删除重复数据,建议在临时表中操作,提高性能

CREATE TABLE 临时表 AS  (select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1) ;
delete from 表名 a where 字段1,字段2 in (select 字段1,字段2 from 临时表);

4、为了提高性能,可以在建立临时表时不要LOG和索引;并分析表。

1).通过create table ... as select将不重复的记录重建成表T_TEST_1
create table T_TEST_1 nologging tablespace &tablespace_name as
select col_id1, col_id2, col_3, col_4, col_5
  from (select col_id1,
               col_id2,
               col_3,
               col_4,
               col_5,
               updatetime,
               row_number() over(partition by col_id1, col_id2 order by updatetime desc) rn
          from T_TEST)
 where rn = 1
2).对新表重建索引,原表有多少索引,在新表上也重建多少索引
create index IND_T_TEST_1 on T_TEST_1(col_id1, col_id2)
nologging tablespace &ind_tablespace_name;
3).收集新表统计信息,确保SELECT查询采用正确高效率的执行计划
declare
BEGIN
  dbms_stats.gather_table_stats(ownname          => '&user',
                                tabname          => 'T_TEST_1',
                                estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                cascade          => true,
                                method_opt       => 'FOR ALL COLUMNS SIZE 1',
                                granularity      => 'all');
END;
/
4).将新表和新索引更改为日志方式
alter table T_TEST_1 logging;
alter index IND_T_TEST_1 logging;
5.备份旧表,将新表切换上线
alter table T_TEST rename to T_TEST_BAK0902;
alter table T_TEST_1 rename to T_TEST;
 
不建议方案:不建议直接在原表T_TEST上做DELETE操作

 

===================================分割线=========================================

 

 

 

oracle里可以用 row_number()连子查询进行处理

 

select distinct table1.id,table1.name

 

from (select a.id,b.name, row_number() over (partition by c.wzbah order by b.id desc) rn
from T1 a,T2 b

 

where a.id = b.id ) table1
where rn = 1

 

 

 

ps:

 

partition by 是用后面字段进行分割, rn是行号

 

这样就只取到行号为1的那一行了

 

可以用到max()函数

1、要求,在一个表中,某一字段为重复字段。需要去除重复字段。同时将所有字段显示出来。

 

  SELECT * FROM (select a1,a2,a3,

 

  Row_number() OVER (PARTITION BY a1 ORDER BY a1)  rn

 

  from a

 

  ) where RN = 1

 

 Row_number() OVER (PARTITION BY a1 ORDER BY a1)作用Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法

 

  ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)

 

  RANK() 类似,不过RANK 排序的时候跟派名次一样,可以并列2个第一名之后 是第3名

 

  LAG 表示 分组排序后 ,组内后面一条记录减前面一条记录的差,第一条可返回 NULL

 

  BTW: EXPERT ONE ON ONE 上讲的最详细,还有很多相关特性,文档看起来比较费劲

 

  row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序)

 

  rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)

 

  dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。

 

  相比之下row_number是没有重复值的

 

  lag(arg1,arg2,arg3):

 

  arg1是从其他行返回的表达式

 

  arg2是希望检索的当前行分区的偏移量。是一个正的偏移量,时一个往回检索以前的行的数目。

 

  arg3是在arg2表示的数目超出了分组的范围时返回的值。

 

 

=====================================分割线========================================

 

查询及删除重复记录的SQL语句
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)

注:rowid为oracle自带不用该.....

3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
(二)
比方说
在A表中存在一个字段“name”,
而且不同记录之间的“name”值有可能会相同,
现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

(三)
方法一
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0 方法二
"重复记录"有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。
  1、对于第一种重复,比较容易解决,使用
select distinct * from tableName
  就可以得到无重复记录的结果集。
  如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
  发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。
  2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下
  假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
  最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)
(四)
查询重复
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1
)

分享到:
评论

相关推荐

    oracle去重语句

    oracle去重语句 ,批量设置,去除重复以及空格racle去重语句racle去重语句

    oracle字段去重

    ### Oracle字段去重详解 #### 一、Oracle字段去重概念与应用场景 在数据库操作过程中,数据表中可能会出现重复的数据记录,这不仅浪费存储空间,还可能导致数据分析时出现错误的结果。因此,去除重复记录是数据库...

    oracle本表去重的语句优化

    在Oracle数据库中,进行表内数据去重是一项常见的任务,特别是在处理大量数据时。面对一个包含一亿条记录,且每个记录只有一个字段`mobile`的`bao_mobile_temp`表,如何高效地去除重复数据,是本文要探讨的核心问题...

    OracleSQL实例-删除重复数据行留最新日期实例.pdf

    在Oracle SQL中,删除重复数据并保留最新日期的实例是一个常见的数据清理任务,尤其是在具有历史记录或事务数据的数据库中。这个实例展示了如何通过SQL查询有效地处理这种情况。在这个例子中,我们有一个名为`abc`的...

    oracle-字符串去重函数

    传入一个字符串和该字符串的分割字符,返回去重后的字符串,可以直接在plsql中运行,简单的函数运用,能处理oracle中。资源仅供参考

    oracle 删除去掉重复项

    在Oracle数据库管理中,处理数据冗余和删除重复项是一项重要的任务,这有助于维护数据的完整性和提升查询效率。本文将深入探讨Oracle中删除和处理重复数据的方法,特别是基于提供的内容,我们将详细解析如何通过SQL...

    oracle连续重复行去重

    ### Oracle连续重复行去重详解 在Oracle数据库中,处理连续重复行的去重操作是一项常见但又具有一定挑战性的任务。特别是当需要合并特定条件下连续出现的记录时,这一过程会更加复杂。本文将深入探讨如何实现...

    wm_concat函数DDL.zip

    在Oracle数据库中,`wm_concat`函数曾是一个非常实用的工具,用于将多个行的数据合并成单个字符串,尤其在需要进行数据汇总时非常方便。然而,从Oracle 11g版本开始,出于性能和安全性的考虑,Oracle官方取消了这个...

    解决Oracle删除重复数据只留一条的方法详解

    在Oracle数据库管理中,有时我们需要清理表中的重复数据,确保数据的唯一性和准确性。本文将详细探讨如何在Oracle中删除重复数据,仅保留一条记录。这个过程通常涉及到查询重复数据,然后选择要保留的特定记录,并...

    Oracle表中重复数据去重的方法实例详解

    在Oracle数据库管理中,处理重复数据是常见的任务,特别是在数据清洗和数据分析阶段。本文将深入探讨Oracle表中如何有效地去除重复数据,通过实例详细解析这一过程。 首先,我们需要理解什么是重复数据。在数据库中...

    oracle删除重复数据的sql语句

    根据给定文件中的标题、描述、标签以及部分内容,本文将详细介绍在Oracle数据库中删除重复数据的几种方法。这些方法利用了ROWID、GROUP BY、DISTINCT等关键字来实现数据去重的功能。 ### 1. 使用ROWID进行去重 ...

    oracle实现行转列功能,并使用逗号进行隔开拼接,成为一条数据.pdf

    Oracle 实现行转列功能并使用逗号进行隔开拼接成为一条数据 Oracle 中实现行转列功能,并使用逗号进行隔开拼接,成为一条数据是指将多行数据合并成一行数据,并用逗号分隔每个字段的值。这种功能在实际应用中非常...

    Oracle经典面试总结-去重-附答案.pdf

    Oracle经典面试总结 本文档总结了 Oracle 数据库相关的面试问题和答案,涵盖了 optimizer_mode、CBO、RBO、索引、排序、表空间、回滚段、绑定变量、SQL 优化等多个方面的知识点。 _optimizer_mode_ Optimizer_...

    sql_按照某一个字段进行去重后获取全部字段

    根据提供的文件信息,本文将详细解释如何通过 SQL 语句实现按照某一字段去重后获取所有字段的操作。在实际工作中,我们经常会遇到需要对数据表中的记录进行去重的情况,尤其是当某个字段作为唯一标识符时,我们希望...

    oracle中将列拼接字段函数

    在Oracle数据库中,进行数据处理时经常会用到各种各样的函数来满足业务需求。其中,字符串拼接操作是一项非常常见的需求,特别是在需要将多个列值组合成一个单独的字符串时。本文将详细介绍如何使用Oracle中的`WMSYS...

    SQL去重 清除冗余数据

    使用distinct后,Oracle就会在查找的时候对后面的字段进行去重排序,这会对查找的效率带来影响。 删除重复记录可以使用delete语句,例如:delete from Lin_test a where a.id not in (select min(id) from Lin_test...

    cmd创建用户及表数据去重

    根据提供的标题、描述、标签以及部分内容,我们可以提炼出与“cmd创建用户及表数据去重”相关的几个核心知识点,包括如何在命令行模式下创建数据库用户、导入数据、以及实现表数据去重的方法。 ### 一、CMD命令行...

    ORACLE CRC32函数

    ### ORACLE CRC32函数详解 #### 一、概述 在Oracle数据库中,`CRC32`函数是一种非常实用的功能,主要用于将字符类型的数据转换为一个唯一的数字类型,这一过程通常被称为散列(Hash)。通过该函数,可以方便地生成...

Global site tag (gtag.js) - Google Analytics