select查询重复记录的sql语句百家了乐八大技巧:

 百家乐-数据     |      2020-03-24 07:05

在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不另行的结果集

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