终极一个select即获得了Name,Address不重复的结果集

1、对于第一种重复,相比易于驱除,使用

借使有重复的字段为Name,Address,供给获得那五个字段独一的结果集select
identity(int,1,1State of Qatar as autoID, * into #Tmp from tableNameselect
min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoIDselect
* from #Tmp where autoID in(select autoID from #tmp2)

5、查找表中多余的重复记录,不带有rowid最小的记录

1、对于第一种重复,相比较便于化解,使用select distinct * from tableName

代码如下复制代码 select distinct * from tableName

方法二

就足以得到无重复记录的结果集。

在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
integerdeclare cur_rows cursor local for select 主字段,count(*) from
表名 group by 主字段 having count(*) ; 1open cur_rowsfetch cur_rows
into @id,@maxwhile @@fetch_status=0beginselect @max = @max -1set
rowcount @maxdelete from 表名 where 主字段 = @idfetch cur_rows into
@id,@maxendclose cur_rowsset rowcount 0

代码如下复制代码 declare @max integer,@id integerdeclare cur_rows
cursor local for select 主字段,count(*) from 表名 group by 主字段
having count(*) ; 1open cur_rowsfetch cur_rows into @id,@maxwhile
@@fetch_status=0beginselect @max = @max -1set rowcount @maxdelete from
表名 where 主字段 = @idfetch cur_rows into @id,@maxendclose
cur_rowsset rowcount 0

(四卡塔尔查询重复select * from tablename where id in (select id from
tablename group by id having count(id) 1

产生这种重新的原故是表设计不周产生的,扩充独一索引列就能够消灭。

2、那类重复难题普通供给保留重复记录中的第一条记下,操作方法如下

代码如下复制代码 delete from people where peopleId in (select peopleId
from people group by peopleId having count(peopleId卡塔尔国 1State of Qatarand rowid not in
(select min(rowid卡塔尔 from people group by peopleId having count(peopleId
State of Qatar1卡塔尔

就足以博得无重复记录的结果集。

代码如下复制代码 select distinct * into #Tmp from tableNamedrop table
tableNameselect * into tableName from #Tmpdrop table #Tmp

发生这种重新的原故是表设计不周发生的,扩张唯一索引列就可以消除。

1、查找表中多余的重复记录,重复记录是借助单个字段来决断

借使该表必要删除重复的记录,可以按以下办法删除select distinct * into
#Tmp from tableNamedrop table tableNameselect * into tableName from
#Tmpdrop table #Tmp

代码如下复制代码 Select Name,Count(*) From A Group By Name Having
Count(*) 1

有几个意思上的重复记录,一是一点一滴重复的记录,也即怀有字段均再一次的记录,二是局地重大字段重复的记录,比如Name字段重复,而任何字段不认定再度或都再也能够忽视。

假若该表须求删除重复的笔录,能够按以下办法删除

代码如下复制代码 select * from peoplewhere peopleId in (select peopleId
from people group by peopleId having count(peopleId) 1)

(四卡塔尔(قطر‎查询重复

4、删除表中多余的重复记录,只留有rowid最小的记录

代码如下复制代码 select identity(int,1,1卡塔尔(قطر‎ as autoID, * into #Tmp from
tableNameselect min(autoID) as autoID into #Tmp2 from #Tmp group by
Name,autoIDselect * from #Tmp where autoID in(select autoID from
#tmp2)

代码如下复制代码 select * from vitae awhere (a.peopleId,a.seq) in
(select peopleId,seq from vitae group by peopleId,seq having count(*)
1)

相关文章