查看内容

select查询重复记录的sql语句

  • 2020-03-17 09:02
  • 数据库知识
  • Views

在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, *澳门太阳集团2007网站, 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

上一篇:sql多表联查实例澳门太阳集团2007网站 下一篇:没有了