在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

方法二

有四个意思上的重复记录,一是全然重复的笔录,也即具有字段均再一次的笔录,二是一对要害字段重复的记录,比如Name字段重复,而任何字段不断定再一次或都再度能够忽视。

1、对于第一种重复,比较轻易搞定,使用select distinct * from tableName

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

假诺该表必要删除重复的记录,能够按以下情势删除select distinct * into
#Tmp from tableNamedrop table tableNameselect * into tableName from
#Tmpdrop table #Tmp

发出这种重新的原由是表设计不周产生的,增添独一索引列就能够缓慢解决。

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

假诺有重复的字段为Name,Address,供给赢得那八个字段独一的结果集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即得到了Name,Address不重复的结果集

(四State of Qatar查询重复select * from tablename where id in (select id from
tablename group by id having count(id) 1

网站地图xml地图