方法二

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

2、那类重复难点平常须要保存重复记录中的第一条记下,操作方法如下

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

最后八个select即得到了Name,Address不另行的结果集

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

就足以获取无重复记录的结果集。

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

借使有重复的字段为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)

就足以拿走无重复记录的结果集。

1、对于第一种重复,比较容易解决,使用select distinct * from tableName

小说介绍利用having group by 及各类select in
联合查询来达成的三种区别方法的过滤重复记录的sql语名。

若果该表须要删除重复的笔录,能够按以下方法删除select distinct * into
#Tmp from tableNamedrop table tableNameselect * into tableName from
#Tmpdrop table #Tmp

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

发生这种重新的来头是表设计不周爆发的,增添唯一索引列就可以解决。

3、查找表中多余的重复记录

在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

代码如下复制代码 delete from vitae awhere (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)

有三个意思上的重复记录,一是完全重复的记录,也即怀有字段均再一次的笔录,二是一些主要字段重复的记录,比如Name字段重复,而其他字段不自然再次或都重复能够忽视。

代码如下复制代码 delete from people where peopleId in (select peopleId
from people group by peopleId having count(peopleId卡塔尔国 1卡塔尔(قطر‎and rowid not in
(select min(rowidState of Qatar from people group by peopleId having count(peopleId
卡塔尔(قطر‎1卡塔尔国

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

发生这种重新的缘由是表设计不周发生的,扩大独一索引列就可以扫除。

一经该表要求删除重复的笔录,可以按以下措施删除

代码如下复制代码 select * from vitae awhere (a.peopleId,a.seq) in
(select peopleId,seq from vitae group by peopleId,seq having count(*威澳门尼斯人36366com,)
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

代码如下复制代码 select * from vitae awhere (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)

代码如下复制代码 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)

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注