`
liyanhui
  • 浏览: 349799 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

oracle 查询出每组中最大的三个记录和查询重复记录

阅读更多
如何写一个sql语句,查询出每组中最大的三个记录:
如:
sqlplus> select deptno,empno from emp order by deptno,empno desc

    DEPTNO      EMPNO
---------- ----------
        10       7934
        10       7839
        10       7782
        20       7902
        20       7876
        20       7788
        20       7566
        20       7369
        30       7900
        30       7844
        30       7698
        30       7654
        30       7521
        30       7499

写一个sql语句,结果是:
        10       7934
        10       7839
        10       7782
        20       7902
        20       7876
        20       7788
        30       7900
        30       7844
        30       7698

即:得到每组中三个最大的empno值。


11:46:50 SQL> select deptno,empno
11:47:21   2  from (
11:47:22   3    select deptno,empno,rank() over (partition by deptno order by empno desc) rank_no
11:48:02   4    from scott.emp
11:48:09   5  ) where rank_no <= 3
11:48:17   6  /

    DEPTNO      EMPNO
---------- ----------
        10       7934
        10       7839
        10       7782
        20       7902
        20       7876
        20       7788
        30       7900
        30       7844
        30       7698

9 rows selected.

Elapsed: 00:00:00.01
11:48:18 SQL>

------------------------------------------------------
二:查询重复记录
select *
from scott.emp a
where rowid !=(
select max(rowid) from scott.emp b
where a.DEPTNO=b.DEPTNO  and a.EMPNO =b.EMPNO
)


----------------------------------------------
下面内容来自http://www.cnblogs.com/no7dw/archive/2009/10/31/1593440.html
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)



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查询重复数据与删除重复记录方法

    此查询语句通过子查询找出每组重复记录中ROWID最小的那条记录,并在外部查询中筛选出除ROWID最小之外的其他重复记录。 ##### 2. 使用GROUP BY方法 **查询重复数据示例** 使用`GROUP BY`方法时,可以通过计算每组...

    Oracle查询重复数据与删除重复记录方法(txt)

    本文将详细介绍几种常用的查询和删除Oracle数据库中重复记录的方法。 #### 一、查询重复数据 1. **使用ROWID** ROWID是Oracle提供的一种特殊数据类型,它表示表中行的物理地址。通过ROWID可以快速定位到表中的某...

    oracle删除重复记录

    在Oracle数据库管理中,处理数据冗余是一项非常重要的工作,特别是在大数据量的环境中,重复记录不仅浪费存储空间,还可能导致数据分析结果出现偏差。本文将详细介绍如何在Oracle数据库中有效地删除表内的重复记录。...

    oracle复杂查询练习题

    从给定的文件信息中,我们可以提炼出三个与Oracle数据库查询相关的知识点,下面将对这些知识点进行详细解析。 ### 知识点一:删除表中重复记录(无主键情况) 在Oracle数据库中,如果一个表没有定义主键,但其中...

    如何高效删除Oracle数据库中的重复数据

    - **解释**:这种方法利用ROWID来直接删除除每组中的最大ROWID外的所有重复记录。 2. **使用临时表** - **示例代码**: ```sql CREATE TABLE temp_table AS SELECT column1, column2, MAX(rowid) dataid FROM...

    oracle去重复

    - 该查询能有效地去除基于`term_id`和`user_deptno`的重复记录,并保留每组中的第一条记录。 #### 三、扩展示例 另一个示例展示了更复杂的条件: ```sql select t2.user_deptno, t2.deptfourname, count(*) from ...

    oracle数据库各种删除重复数据方法

    HAVING COUNT(*) &gt; 1)**: 这个子查询同样是对记录按ID分组,但它只选择每个ID组中的最小ROWID,即每组的第一条记录。通过这种方式,可以确保每组中至少保留一条记录。 3. **主查询**: 主查询使用上述两个子查询的...

    oracle连续重复行去重

    ### Oracle连续重复行去重详解 在Oracle数据库中,处理连续重复行的去重操作是一项常见但又具有一定挑战性的任务。...通过这些步骤和技术的应用,我们可以有效地处理数据库中连续重复记录的问题。

    如何查出同一张表中字段值重复的记录

    主要内容包括使用三种方法:ROWID、GROUP BY 和 DISTINCT,来查找和删除表中的重复记录。 ### 一、ROWID 方法 #### 1.1 查找重复记录 ROWID 是 Oracle 数据库中用于唯一标识表中每一条记录的一种特殊类型。可以...

    最高效的删除重复记录方法

    当存在多个字段组合能唯一标识一条记录时,可以使用子查询结合 GROUP BY 和 HAVING 子句来找到重复记录,然后利用这些信息来删除重复项。例如: ```sql DELETE FROM people WHERE peopleId IN (SELECT peopleId ...

    oracle多表查询PPT课件.pptx

    Oracle数据库的多表查询是数据库操作中的核心技能之一,它涉及到如何从两个或更多个相关表中检索数据。本章的PPT课件主要讲解了如何进行多表查询,包括等值连接、非等值连接、外连接以及自连接等多种连接类型。 1. ...

    sql重复记录操纵汇总

    在Oracle中,可以通过比较ROWID来保留每组重复记录中的一个。查询和删除语句如下: ```sql -- 查询 SELECT * FROM table1 a WHERE rowid != (SELECT MAX(rowid) FROM table1 b WHERE a.name1 = b.name1 AND a...

    删重复记录

    接着,它通过子查询找出每个重复组中的最大`ROWID`,然后在外层查询中删除那些`ROWID`不在这些最大值之列的记录。这种方法保留了每个重复组中具有最大`ROWID`的那条记录。 2. **使用`ROWID`和比较大小方法:** ``...

    oraclesql面试题

    从给定的Oracle SQL面试题中,我们可以提炼出多个关键知识点,这些知识点涵盖了SQL查询、数据操作、表结构设计以及Oracle数据库的一些高级特性。以下是对这些知识点的详细解析: ### 1. 数据分组与聚合函数 在第一...

    oracle字段去重

    1. 子查询通过`ROW_NUMBER()`函数为每个`TERM_ID`组内的记录分配一个行号。 2. 外层查询筛选出行号为1的记录,即每个`TERM_ID`对应的首条记录。 3. 最后对筛选后的结果按照`USER_DEPTNO`和`DEPTFOURNAME`分组并计数...

    Oracle培训记录(最终版)

    根据给定的“Oracle培训记录(最终版)”文件,我们可以从中提炼出一系列关于Oracle数据库管理和操作的关键知识点,这些知识涵盖了从基础SQL语句到高级的数据库管理技术,以下是对这些知识点的详细解读: ...

    oracle查询命令总结

    Oracle查询命令是数据库管理中非常基础且重要的技能,以下是对标题和描述中涉及的Oracle查询命令的详细说明: 1. **选择特定部门的雇员**: ```sql select * from emp where deptno=30; ``` 这条SQL语句用于...

    Oracle操作语句大全

    - **SELECT * FROM DUAL**:查询`DUAL`表的所有列,由于`DUAL`表只有一个记录,因此该查询结果也只有一个记录。 - **SELECT SYSDATE FROM DUAL**:查询当前系统的日期和时间,`SYSDATE`是Oracle中的一个内置函数,...

Global site tag (gtag.js) - Google Analytics