Oracle查询表里的重复数据方法

格式：pdf
大小：104.43 KB
文档页数：4

Oracle查询表⾥的重复数据⽅法

⼀、背景

⼀张person表，有id和name的两个字段，id是唯⼀的不允许重复，id相同则认为是重复的记录。

⼆、解决

select id from group by id having count(*) > 1

按照id分组并计数，某个id号那⼀组的数量超过1条则认为重复。

如何查询重复的数据

select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1

PS：将上⾯的>号改为=号就可以查询出没有重复的数据了。

Oracle删除重复数据的SQL（删除所有）：

删除重复数据的基本结构写法：

想要删除这些重复的数据，可以使⽤下⾯语句进⾏删除

delete from 表名 a where 字段1,字段2 in

(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)

上⾯的SQL注意：语句⾮常简单，就是将查询到的数据删除掉。不过这种删除执⾏的效率⾮常低，对于⼤数据量来说，可能会将数据库吊死。

建议先将查询到的重复的数据插⼊到⼀个临时表中，然后对进⾏删除，这样，执⾏删除的时候就不⽤再进⾏⼀次查询了。如下：

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 临时表);

Oracle删除重复数据的SQL（留下⼀条记录）：

oracle中，有个隐藏了⾃动rowid，⾥⾯给每条记录⼀个唯⼀的rowid，我们如果想保留最新的⼀条记录，我们就可以利⽤这个字段，保留重复数据中rowid最⼤的⼀条

记录就可以了。

使⽤ROWID查询重复数据：

select a.rowid,a.* from 表名 a

where a.rowid !=

(select max(b.rowid) from 表名 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2 )

括号中的SQL查询出rowid最⼤的记录，⽽外⾯就是查询出除了rowid最⼤之外的其他重复的数据了。

由此，我们要删除重复数据，只保留最新的⼀条数据，就可以这样写了：

删除重复数据（留下最⼤ROWID的⼀条）

delete from 表名 a where a.rowid !=

(select max(b.rowid) from 表名 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2 )

删除重复数据（留下最⼩ROWID的⼀条）

delete tab t where t.rowid > (

select min(t2.rowid) from tab t2 where t.col2 = t2.col2 and t.col8 = t2.col8

)

当然，上⾯语句的执⾏效率是很低的，可以考虑建⽴临时表，讲需要判断重复的字段、rowid插⼊临时表中，然后删除的时候在进⾏⽐较。

create table 临时表 as select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.字段1,a.字段2;

delete from 表名 a

where a.rowid !=

(select b.dataid from 临时表 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2 );

commit;

对于完全重复记录的删除

对于表中两⾏记录完全⼀样的情况，可以⽤下⾯语句获取到去掉重复数据后的记录：

select distinct * from 表名

可以将查询的记录放到临时表中，然后再将原来的表记录删除，最后将临时表的数据导回原来的表中。如下：

CREATE TABLE 临时表 AS (select distinct * from 表名);

truncate table 正式表;

insert into 正式表 (select * from 临时表);

drop table 临时表;

如果想删除⼀个表的重复数据，可以先建⼀个临时表，将去掉重复数据后的数据导⼊到临时表，然后在从临时表将数据导⼊正式表中，如下：

INSERT INTO t_table_bakselect distinct * from t_table;

MySQL查询及删除重复记录的⽅法

查询及删除重复记录的⽅法（例⼦演⽰）

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from table where tableId in (select tableId from table group by tableId having count(tableId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（tableId）来判断，只留有rowid最⼩的记录。

delete from table where tableId in (select tableId from table group by tableId having count(tableId) > 1) and rowid not in (select min(rowid) from table group by tableId having count(tableId )>1)

3、查找表中多余的重复记录（多个字段）

select * from vitae a where (a.tableId,a.seq) in (select tableId,seq from vitae group by tableId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最⼩的记录

delete from vitae a where (a.tableId,a.seq) in (select tableId,seq from vitae group by tableId,seq having count(*) > 1)

and rowid not in (select min(rowid) from vitae group by tableId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最⼩的记录

select * from vitae a where (a.tableId,a.seq) in (select tableId,seq from vitae group by tableId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by tableId,seq having count(*)>1)

6、查询出⼀个表中，某⼀列的值是相同的SQL：

⽐⽅说

在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

oracle查询表中字段⾥数据是否有重复

查单个字段：

SELECT TEST_NAME,COUNT(*) FROM T_TEST GROUP BY TEST_NAME HAVING COUNT(*) > 1

Oracle查询重复数据并删除，只保留⼀条记录

1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断

select

* from 表 where Id in (select Id from 表 group byId having count(Id) >

2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最⼩的记录

DELETE

from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND

ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) >

1);

3、查找表中多余的重复记录（多个字段）

select

* from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having

count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最⼩的记录

delete

from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having

count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq

having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最⼩的记录

select

* from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having

count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq

having count(*)>1)

⽐如现在有⼀⼈员表（表名：peosons）

若想将姓名、⾝份证号、住址这三个字段完全相同的记录查询出来

select p1.*

from persons p1,persons p2

where p1.id<>p2.id

and p1.cardid = p2.cardid and p1.pname = p2.pname and

p1.address = p2.address

可以实现上述效果。

⼏个删除重复记录的SQL语句

1.⽤rowid⽅法

2.⽤group by⽅法

3.⽤distinct⽅法

oracle常见面试题

Oracle常见面试题

1、对数据库SQL2005、ORACLE熟悉吗？

SQL2005是微软公司的数据库产品。是一个RDBMS数据库，一般应用在一些中型数据库的应用，不能跨平台。

ORACLE是ORACLE公司的数据产品，支持海量数据存储，支持分布式布暑，支持多用户，跨平台，数据安全完整性控制性能优越，是一个ORDBMS，一般用在大型公司。

2、能不能设计数据库？如何实现数据库导入与导出的更新

使用POWERDISINE工具的使用，一般满足第三范式就可以了。EXP与IMP数据库的逻辑导入与导出

3、如何只显示重复数据，或不显示重复数据

显示重复：select * from tablename group by id having count(*)>1

不显示重复：select * from tablename group by id having count(*)=1

4、什么是数据库的映射

就是将数据库的表与字段对应到模型层类名与属性的过程.

5、写分页有哪些方法，你一般用什么方法？用SQL语句写一个分页？

如何用存储过程写分页？

在SQLSERVER中使用TOP分页，在ORACLE中用ROWNUM，或分析函数ROW_NUMBER

使用TOP：

select top 20,n.* from tablename n minus select top 10,m.* from tablename m

使用分析函数：

select * from

(select n.*,row_number() over(order by columnname) num from tablename n)

where num>=10 and num <=20;

使用过程时，只要将分页的范围用两个参数就可以实现。在ORACLE中，要将过程封装在包里，还要用动态游标变量才能实现数据集的返回。

ORACLE

ＯＲＡＣＬＥ数据库管理经验　

口李政　作为ＯＲＡＣＬＥ的ＤＢＡ，在管理数据库的过程　中，我们常常会遇到一些问题，下面就可能遇到的　问题给出相应的解决办法。以下的操作在拥有相应　权限下才能进行。　１、如何确定一个表所在的表空间　（ｔａｂｌｅｓｐａｃｅ）？　ＳＥＬＥＣＴ　ｔａｂｌｅｓｐａｃｅ＿ｎａｍｅ　ＦＲＯＭ　ａｌｌ＿ｔａｂｌｅｓ　ＷＨＥＲＥ　ｔａｂｌｅ＿ｎａｍｅ＝、ＹＯＵＲＴＡＢＬＥＮＡＭＥ　：　注意：ＹＯＵＲＴＡＢＬＥＮＡＭＥ大写。　２、怎样从一个表中删除重复的行？　如果表中建立了主键或唯一键，就很容易用下　面的ＳＱＬ语句删除这些记录：　ＤＥＬＥＴＥ　ＦＲ０Ｍ　ｔａｂｌｅｎａｍｅ　ＷＨＥＲＥ　ｒｏｗｉｄ　ｎｏｔ　ｉｎ　（ＳＥＬＥＣＴ　ＭＩＮ（ｒｏｗｉｄ）ＦＲＯＭ　ｔａｂｌｅｎａｍｅ　ＧＲＯＵＰ　ＢＹ　ｃｏｌｕｍｎｌ，ｃｏｌｕｍｎ２，ｃｏｌｕｍｎ３…）；　这里ｃｏｌｕｍｎｌ，ｃｏｌｕｍｎ２，ｃｏｌｕｍｎ３……组成识别　每一记录的键。如果表中没有建立主键或唯一键，　你可以用下面的语句生成一个临时表：　ＣＲＥＡＴＥ　ＴＡＢＬＥ　ｔｅｍｐｔａｂｌｅｎａｍｅ　ＡＳ　ＳＥＬＥＣＴ　ＤＩＳＴＩＮＣＴ￥ＦＲＯＭ　ｔａｂｌｅｎａｍｅ；　然后删掉原始表再将临时表取名原表即可。　３、如何识别和删除无效的子记录以激活或生　成一个外键（当你试图激活或生成子表和父表之问　的关联时产生“Ｐａｒｅｎｔ　ｋｅｙｓ　ｎｏｔ　Ｆｏｕｎｄ”错误！）？　这些无效的记录可以用下面的ＳＱＬ语句识别　和删除：　ＤＥＬＥＴＥ　ＦＲＯＭ　ｃｈｉｌｄｔａｂｎａｍｅ　ｅ　ＷＨＥＲＥ　ＮＯＴ　ＥＸＩＳＴＳ（ＳＥＬＥＣＴ、Ｘ　ＦＲ０Ｍ　ｐａｒｅｎｔｔａｂｎａｍｅ．ｊ　ＷＨＥＲＥ　ｅ．ｋｅｙｃｏｌｕｍｎ１＝Ｐ．ｋｅｙｃｏｌｕｍｎ１…１　将上面语句中的ＤＥＬＥＴＥ改为ＳＥＬＥＣＴ即可列　出那些无效的记录。　４、如何知道表的总记录数？　下面一条简单的查询语句就可以知道一个表的　总记录数：　ＳＥＬＥＣＴ　ＣＯＵＮＴ（￥）ＦＲＯＭ　ｔａｂｌｅｎａｍｅ：　如果你想知道多个表各自的记录数，你可以　ＳＥＬＥＣＴ　ＴＡＢＩ　Ｅ　ＮＡＭＥ，ＮＵＭ＿ＲＯＷＳ　ＦＲＯＭ　ＡＬＬＴＡＢＬＥＳ　ＷＨＥＲＥ　ＴＡＢＬＥ　ＮＡＭＥ　ＬＩＫＥ、ｓｅａｒｃｈｓｔｒｉｎｇ　；　你还可以用下面这样一个ＳＱＬ脚本来完成：　ＳＥＴ　ＰＡＧＥＳ　０　ＦＥＥＤＢＡＣＫ　ＯＦＦ　ＥＣＨＯ　ＯＦＦ　ＶＥＲＩＦＹ　ＯＦＦ　ＴＥＲＭＯＵＴ　ＯＦＦ　ＳＰＯＯＬ　ＣＯＵＮＴ．ＳＱＩ　ＳＥＬＥＣＴ、ＳＥＬＥＣＴ　ＣＯＵＮＴ（￥）ＦＲＯＭ、Ｉｌ　ＴＡＢＩ　ＥＮＡＭＥ　Ｉｌ　；　ＦＲＯＭ　ＡＬＬＴＡＢＬＥＳ　ＷＨＥＲＥ　ＴＡＢＬＥ＿ＮＡＭＥ　ＬＩＫＥ、ｓｅａｒｃｈｓｔｒｉｎｇ　；　ＳＰ００Ｌ　０ＦＦ　ＳＥＴ　ＴＥＲＭＯＵＴ　ＯＮ　＠ＣＯＵＮＴ．ＳＱＬ　ＳＥＴ　ＦＥＥＤＢＡＣＫ　ＯＮ　ＶＥＲＩＦＹ　ＯＮ　ＰＡＧＥＳ　２４　５、如何确定当前的ｓｅｓｓｉｏｎ　ｉｄ？　在视图ｖ￥ｓｅｓｓｉｏｎ中可以发现ｕｓｅｒｎａｍｅ、ｐｒｏ—　ｇｒａｍ、ｍａｃｈｉｎｅ、ｔｅｒｍｉｎａｌ、ｓｅｓｓｉｏｎ　ｉｄ、ｓｅｒｉａｌ＃以及其　他更多东西。这个视图中有ａｕｄｓｉｄ这样的一列，当　你这一列的值跟ＵＳｅｒｅｎｖ（、ｓｅｓｓｉｏｎｉｄ　）联系起来　时，可以得到你当前ｓｅｓｓｉｏｎ的相关信息。相应的　查询语句是：　ＳＥＬＥＣＴ　ＵＳＥＲＮＡＭＥ，ＳＩＤ，ＳＥＲＩＡＩ￣，ＰＲＯＧＲＡＭ　ＦＲＯＭ　Ｖ￥ＳＥＳＳＩＯＮ　ＷＨＥＲＥ　ＡＵＤＳＩＤ＝ＵＳＥＲＥＮＶ（、ＳＥＳＳＩＯＮＩＤ　）；　６、如何结束一个ｓｅｓｓｉｏｎ？　用上面的方法发现你想结束的ｓｅｓｓｉｏｎ的ＳＩＤ　和ＳＥＲＩＡＩ￣，然后执行下面的命令：　ＡＬＴＥＲ　ＳＹＳＴＥＭ　ＫＩＬＬ　ＳＥＳＳＩＯＮ、ｓｉｄ，ｓｅｒｉａｌ＃　；　请注意ｓｉｄ和ｓｅｒｉａｌ＃必须在单引号里面并且以　逗号分割。　７、如何知道我正在连接的数据库？以什么用户　连接？　数据库名能在多个视图中发现，其中有一个所　有用户能够读取的视图是ＧＬＯＢＡＬ　ＮＡＭＥ。查询办　法是：　ＳＥＬＥＣＴ　ＧＬＯＢＡＬ＿ＮＡＭＥ　ＦＲＯＭ　

数据仓库面试题

work Information Technology Company.2020YEAR 数据仓库及BI工程师面试题集锦

前言

1、介绍一下项目经验、项目中的角色。

一、数据库

1、 Oracle数据库，视图与表的区别普通视图与物化视图的区别物化视图的作用

i. 视图与表的区别

1. 1、视图是已经编译好的sql语句。而表不是

2. 视图没有实际的物理记录。而表有。

3. 表是内容，视图是窗口

4. 表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改

ii. 物化视图与视图区别

1. 物化视图和视图差别非常大，不是几句能说清物化视图是自动刷新或者手动刷新的，视图不用刷新物化视图也可以直接update，但是不影响base table，对视图的update反映到base table上物化视图主要用于远程数据访问，物化视图中的数据需要占用磁盘空间，视图中不保存数据。

2、 Oracle数据库，有哪几类索引，分别有什么特点？

a) 1.单列索引与复合索引

b) 一个索引可以由一个或多个列组成，用来创建索引的列被称为“索引列”。

c) 单列索引是基于单列所创建的索引，复合索引是基于两列或者多列所创建的索引。

d) 2.唯一索引与非唯一索引

e) 唯一索引是索引列值不能重复的索引，非唯一索引是索引列可以重复的索引。 f) 无论是唯一索引还是非唯一索引，索引列都允许取NULL值。默认情况下，Oracle创建的索引是不唯一索引。

g) 3.B树索引

h) B树索引是按B树算法组织并存放索引数据的，所以B树索引主要依赖其组织并存放索引数据的算法来实现快速检索功能。

i) 4.位图索引

j) 位图索引在多列查询时，可以对两个列上的位图进行AND和OR操作，达到更好的查询效果。

优化sql语句提高oracle执行效率(34种方法)

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：

Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在

FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表

(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

（2）WHERE子句中的连接顺序：

Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录

的条件必须写在WHERE子句的末尾。

（3）SELECT子句中避免使用‘*’：

Oracle在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

（4）减少访问数据库的次数：

Oracle在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等。

（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200。

（6）使用DECODE函数来减少处理时间：

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

（7）整合简单,无关联的数据库访问：

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。

（8）删除重复记录：

最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

下载提示

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Oracle查询表里的重复数据方法

合集下载

oracle常见面试题

ORACLE

数据仓库面试题

优化sql语句提高oracle执行效率(34种方法)

文档推荐

最新文档