1.某表的有一个字段有若干重复数据,请写出sql语句删除该字段的多余重复数据?
2.接上题,如果此表有5000万行,请给出删除去重数据的方案,同时要保证不影响这个表的查询、插入的并发操作?mssql中的2个问答题,想了半天有点模糊,请高手给点思路或例子!
2.接上题,如果此表有5000万行,请给出删除去重数据的方案,同时要保证不影响这个表的查询、插入的并发操作?mssql中的2个问答题,想了半天有点模糊,请高手给点思路或例子!
这里
delete a from tb a where exists(select 1 from tb where name=a.name and id>a.id)
delete a from tb a where exists(select 1 from tb where name=a.name and id<a.id)2.接上题,如果此表有5000万行,请给出删除去重数据的方案,同时要保证不影响这个表的查询、插入的并发操作?事务+锁
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)