在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

一:查询及删除重复记录的SQL语句
1、查找表中多余的重复记录,重复记录是依据单个字段(peopleId)来推断
select * from people
where peopleId in (select   peopleId from   people group by   peopleId
having count(peopleId) > 1)
2、删除表中多余的重复记录,重复记录是依赖单个字段(peopleId)来推断,只留有rowid最小的笔录
delete from people
where peopleId in (select   peopleId from people group by   peopleId  
having count(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId
having count(peopleId )>1)
3、查找表中剩下的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by
peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(八个字段),只留有rowid最小的记录
delete from vitae a
where (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)

方法二

5、查找表中多余的重复记录(八个字段),不带有rowid最小的记录
select * from vitae a
where (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)
(二)
比方说
在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

有五个意思上的重复记录,一是完全重复的记录,也即具备字段均再一次的笔录,二是部分要害字段重复的记录,比方Name字段重复,而任何字段不自然再度或都再也能够忽视。

(三)
方法一
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名
group by 主字段 having count(*) >  1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

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

方法二
"重复记录"有七个意思上的重复记录,一是一点一滴重复的笔录,也即具备字段均再度的记录,二是有个别第一字段重复的笔录,比如Name字段重复,而其余字段不明确再一次或都重新能够忽视。
  1、对于第一种重复,比较简单解决,使用
select distinct * from tableName
  就能够获得无重复记录的结果集。
  若是该表需求删除重复的笔录(重复记录保留1条),能够按以下方法删除
金沙糖果派对网站app,select distinct * into #Tmp from tableName
drop table tableName
金沙糖果派对2015cc,select * into tableName from #Tmp
drop table #Tmp
  发生这种重新的来头是表设计不周发生的,扩大独一索引列就可以缓慢解决。
  2、那类重复难点普通要求保存重复记录中的第一条记下,操作方法如下
  如果有重复的字段为Name,Address,须求获得那五个字段独一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by
Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
  末了一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时能够写在select子句中节约此列)
(四)
查询重复
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1
)

就能够赢得无重复记录的结果集。

Reference: 

假诺该表需求删除重复的笔录,能够按以下方法删除select distinct * into
#Tmp from tableNamedrop table tableNameselect * into tableName from
#Tmpdrop table #Tmp

admin

相关文章

发表评论

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