复制代码 代码如下:
delete WeiBoTopics where Id in(select max(Id) from WeiBoTopics group by WeiBoId,Title having COUNT(*) > 1);
SQL:删除重复数据,只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用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 peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>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)
6.消除一个字段的左边的第一位:
复制代码 代码如下:
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
7.消除一个字段的右边的第一位:
复制代码 代码如下:
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
复制代码 代码如下:
update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) > 1) and seq in (select 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)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 崩坏星穹铁道椒丘遗器推荐 搭配什么遗器好
- 决胜巅峰芙蕾雅技能介绍 英雄背景一览
- 金铲铲之战S11卓尔不群阵容推荐 装备搭配什么好
- dnf无色套是哪几件
- dnf旭旭宝宝红眼装备是什么搭配
- dnf时空石哪里爆的多
- 大受日本玩家欢迎!《剑星》在东京的多家商店被售空
- Steam特惠:EA发行商特卖!《女神异闻录5R》只要100多
- PC《对马岛之魂》来啦!5月最值得期待的15款新游戏
- 云水谣《小调江南1·醉声梦色》APE+CUE
- 新世纪天籁《BRAVENEWMUSIC》[低速原抓WAV+CUE]
- 新世纪天籁《BRAVENEWMUSIC》[低速原抓WAV+CUE]
- 《死亡搁浅2》女主艾丽·范宁新造型:魅力修身晚礼服
- 《哈迪斯2》Steam好评如潮!依旧爽快 全方面进化
- 尼古拉斯·凯奇长子殴打母亲被查 曾被送入精神病院