日期:2011-01-25 23:11:00 来源:本站整理
SQL删除反复记录的四种方法[MSSQL防范]
本文“SQL删除反复记录的四种方法[MSSQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:
在SQL数据库中,常常会碰到反复记录的情形,那么就需求SQL删除反复记录,下面为您摆列了四种SQL删除反复记录的方法,用于差别的情形,但愿对您有所启迪.
1、查找表中多余的反复记录,反复记录是按照单个字段(peopleId)来判断
- select * from people
- where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、SQL删除反复记录,反复记录是按照单个字段(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)
以上是“SQL删除反复记录的四种方法[MSSQL防范]”的内容,如果你对以上该文章内容感兴趣,你可以看看七道奇为您推荐以下文章:
本文地址: | 与您的QQ/BBS好友分享! |
评论内容只代表网友观点,与本站立场无关!
评论摘要(共 0 条,得分 0 分,平均 0 分)
查看完整评论