`

SQL重复记录查询(转载) ,及对大数据量的重复数据优化

    博客分类:
  • SQL
阅读更多

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

例二:
select * from testtable
where numeber in (select number from people group by number having count(number) > 1 )
可以查出testtable表中number相同的记录

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


)
============================分割线,以下是优化SQl==============================================
需要从表A中取得所有字段b相同的数据
如下,该SQL用于查询出数据库中某表的所有重复记录!

比如表A
—————————————————————— 
ID USER     PASS 
—————————————————————— 
1 test1        a 
2 test2        b 
3 test3        c 
4 test3        cc 
5 test3        ccc 
6 test4      d 
7 test5      d 
8 test5      e 
—————————————————————— 
 
用什么方法找出其中USER重复的数据,即
—————————————————————— 
ID USER     PASS 
—————————————————————— 
3    test3     c 
4    test3     cc 
5    test3     ccc 
7    test5     d 
8    test5     e 
 

这只是一个例子,如果表中的数据有几百万条,如何提高查询的效率

SQL语句写了很多种了(如上面所转载的),但速度都很慢。
例如如下SQL,在表中数据量很少时执行速度还是很快的,但是当数据量打到50W时,我至今没有等到它的返回结果....
select id,user,pass 
from A where user in (
 select user  from A   group by user having count(user)>1); 
 
方法一:再ORACLE的环境下
可以考虑用EXISTS条件语句代替IN语句 (速度有所提升)
SELECT A1.ID, A1.USER, A1.PASS    
FROM A A1   
WHERE EXISTS   
(   
    SELECT 1    
   FROM A A2   
   WHERE A1.USER = A2.USER  
    AND A1.ID <> A2.ID   
)  
----------------------------------------------------------
SELECT A1.ID, A1.USER, A1.PASS 
FROM A A1 
WHERE EXISTS 
( 
    SELECT 1 
    FROM A A2 
    WHERE A1.USER = A2.USER 
    AND A1.ID <> A2.ID 
) 
 



方法二:如果是BATCH或者SP,可以考虑作一个如下的中间表B

CREATE TABLE B   
AS    
SELECT    
 USER, COUNT(*) AS USER_COUNT   
FROM A   
GROUP BY USER    
HAVING COUNT(*) > 1   
-------------------------------------
ALTER TABLE B   
 ADD PRIMARY KEY (USER)   
------------------------------------
CREATE TABLE B 
AS 
SELECT 
    USER, COUNT(*) AS USER_COUNT 
FROM A 
GROUP BY USER 
HAVING COUNT(*) > 1 
---------------------------------------
ALTER TABLE B 
  ADD PRIMARY KEY (USER) 
 

然后用以下SQL进行检索

SELECT A.*   FROM A   INNER JOIN B ON  
A.USER = B.USER  


这条执行的效率怎样?

select user    
         from A    
         group by user having count(user)>1  
select user 
          from A 
          group by user having count(user)>1 
 
还有重复的几率有多大,如果超过一半的话,优化死也优化不到哪里去

方法三:可以用left join试试(mysql测试通过),把重复记录做为主表left join 表 

select  A.id, A.user, A.pass    
from  
       (select user    
            from A    
           group by user having count(user)>1   
       ) AS B left join A on B.user=A.user  
 
这几种方法中,根据不同的环境进行优化了,如果重复记录比较大的情况下,推荐使用临时表,但有时增加临时表又需要进行维护,第三种方法left join也是很好的选择了
分享到:
评论

相关推荐

    sql 查询重复数据

    SQL 查询重复数据 在数据库中,查询和处理重复数据是一项常见的任务。本文将详细介绍查询和处理重复数据的方法,包括查找所有重复记录、查找重复记录、删除重复记录等。 查找所有重复记录 使用以下 SQL 语句可以...

    查询重复数据sql语句

    本文详细介绍了如何使用SQL查询表中的重复数据,包括对给出的示例SQL语句进行了逐行解释,并探讨了其他的查询方法。正确地识别和处理重复数据对于维护数据质量至关重要。不同的场景可能需要采用不同的策略和技术。...

    sql去掉重复的数据 完全相同的数据去掉重复 及优化

    ### SQL去掉重复的数据及优化 在数据库管理与应用开发中,数据去重是一个常见的需求,尤其是在数据清洗、数据整合等场景下尤为重要。本文将详细探讨如何利用SQL查询语句来去除表中的重复记录,并在此基础上进一步...

    sql优化,大数据量如何解决.docx

    索引是SQL优化的基础技术之一,索引可以加速查询速度、提高数据检索效率。常见的索引类型有: 1. 普通索引(Normal Index) 2. 位图索引(Bitmap Index) 3. 唯一索引(Unique Index) 4. 函数索引(Function-based...

    SQL语句查询重复记录大全

    根据提供的标题、描述以及部分代码内容,我们可以整理出关于如何使用SQL语句查询及处理重复记录的相关知识点。本文将详细阐述这些知识点,并提供具体的SQL语句示例。 ### SQL语句查询所有重复记录 #### 1. 查询...

    C# 用SQL自动记录数据并且查询出某一列有哪些重复数据

    本主题聚焦于如何在C#中利用SQL来自动记录数据,并查询出某一列中的重复数据。以下将详细讲解这一过程,以及相关知识点。 首先,我们需要理解C#和SQL的基础。C#是一种面向对象的编程语言,广泛应用于开发Windows...

    删除表中重复数据sql语句

    - **单字段重复数据查询** ```sql SELECT * FROM people WHERE peopleId IN ( SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(peopleId) &gt; 1 ) ``` - **多字段重复数据查询** ```sql ...

    sql查询表中某字段下所有重复的记录

    sql查询表中某字段下所有重复的记录,删除某字段下重复记录最小的那笔记录

    SQLServer数据查询的优化方法

    随着数据量的增长,如何优化SQLServer中的数据查询成为提升应用程序性能的关键因素之一。本文将探讨影响SQLServer数据查询效率的因素,并提出相应的优化方法。 #### 二、影响查询效率的因素 1. **缺乏有效的索引**...

    SqlServer快速获得表总记录数(大数据量)

    SqlServer快速获得表总记录数(大数据量) 千万级数据小case

    sqlServer删除重复数据

    SQL Server 删除重复数据 SQL Server 删除重复数据是数据库管理中的一项重要任务。重复数据可能是完全重复的记录,也可能是部分关键字段重复的记录。删除重复数据可以使用多种方法,包括使用 SELECT DISTINCT 语句...

    MySQL根据某一个或者多个字段查找重复数据的sql语句

    sql 查出一张表中重复的所有记录数据 1.表中有id和name 两个字段,查询出name重复的所有数据 select * from xi a where (a.username) in (select username from xi group by username having count(*) &gt; 1) 2、...

    查询及删除重复记录的SQL语句.

    Oracle 数据库提供了一系列 SQL 语句来帮助我们查询和删除这些重复的记录。以下是一些常用的 SQL 技巧,用于处理重复记录的问题。 1. **查询重复记录**: 当我们想找出基于单一字段(如 `Id`)的重复记录时,可以...

    sql+如何去重复数据,数据库去重复数据

    这不仅有助于提高数据质量,还能优化查询性能,确保数据的一致性和准确性。SQL作为一种广泛使用的数据库语言,提供了多种方法来处理重复数据问题。根据提供的标题、描述、标签以及部分内容,我们可以深入探讨如何...

    经常面试到的SQL题(查询重复数据和清除重复数据)

    在SQL中,查询和清除重复...面试中,候选人不仅需要展示他们编写SQL的能力,还要表现出对数据一致性和业务影响的理解。通过以上讨论,我们可以深入理解如何在SQL中处理重复数据,以及在面试中如何有效地展示这些技能。

    使用Oracle查询并删除重复记录的SQL语句

    使用Oracle查询并删除重复记录的SQL语句 在实际应用中,删除重复记录是非常常见的操作,特别是在数据导入、数据整合和数据清洁等场景中。Oracle数据库提供了多种方式来查询和删除重复记录,本文将详细介绍这些方法...

    重复记录查询处理

    本文将详细介绍如何通过SQL语句来查询并删除重复记录。 #### 二、查询重复记录 重复记录的查询通常涉及两种情况:基于单一字段的重复记录查询以及基于多个字段的重复记录查询。 ##### 1. 单一字段的重复记录查询 ...

    SQL语句删除数据表中重复的记录

    在SQL中,可以使用特定的查询语句来删除重复的记录,以保持数据的整洁和准确。以下是如何使用SQL语句来删除数据表中重复记录的详细步骤。 首先,我们来看一个简单的例子,假设我们有一个名为`Repeat`的数据表,其中...

    达梦数据库数据量统计.sql

    达梦数据库数据量统计.sql

Global site tag (gtag.js) - Google Analytics