SQL重复记录查询 查询多个字段、多表查询、删除重复记录的方法

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 

select * from testtable where numeber in (select number from people group by number having count(number) > 1 ) 

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) 

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 

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) 

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) 

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 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 

select distinct * from tableName 

select distinct * into #Tmp from tableName  drop table tableName  select * into tableName from #Tmp  drop table #Tmp 

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 * from tablename where id in (  select id from tablename   group by id   having count(id) > 1  )