mysql多列去重查询_sql重复记录的查询和去重（单多列）

2、删除表中多余的重复记录(多个字段)，只留有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)

3、删表再重建的方式，快糙猛，但可能中断业务的方式去重：

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop

table #Tmp

4、存储过程方式

暂略

*********************************************查询重复***************************************

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子句中省去此列)

3、查找表中多余的重复记录(多个字段)

select * from vitae a

where (a.peopleId,a.seq) in (select peopleId,seq from vitae group

by peopleId,seq having count(*) > 1) 运行会产生问题，where(a.peopleId,a.seq)这样的写发是通不过的？？？！