loading
Zai30
搜 索
登录 | 注册

SQL中重复记录,最常见的7种情况,及查询和处理方法

SQL中重复记录,最常见的7种情况,及查询和处理方法


数据库中数据重复,冗余数据,垃圾数据都是很常见的情况。实际中大家都是怎么处理的呢。今天在zai30.com和大家分享下

1)用SQL语句,删除掉重复项只保留一条?
2)在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断

select * from people 
where peopleId in (
    --将所有peopleId数量大于1的peopleId查询出来
    select peopleId from people group by peopleId having count(peopleId) > 1
)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录

delete from people 
where   peopleName in (
    -- 所有数量大于1的peopleName
    select peopleName    from people group by peopleName      having count(peopleName) > 1
) 
and   peopleId not in (
    -- peopleName数量大于1中最小的peopleId
    select min(peopleId) from people group by peopleName     having count(peopleName)>1
)

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

select * from tablename a 
where (a.peopleId,a.seq) in 
(
    -- 多个字段都重复的记录
    select peopleId,seq from tablename group by peopleId,seq having count(*) > 1
)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

-- 保留rowid最小的重复记录
delete from tablename a 
where (a.peopleId,a.seq) in (
--所有重复记录
select peopleId,seq from tablename group by peopleId,seq having count(*) > 1
) 
and rowid not in (
--重复记录中最小的rowid
select min(rowid) from tablename group by peopleId,seq having count(*)>1
)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

--查询不包含最小rowid的所有重复记录
select * from tablename a 
where (a.peopleId,a.seq) in (
    --多字段重复时,所有的重复记录
    select peopleId,seq from tablename group by peopleId,seq having count(*) > 1
) 
and rowid not in (
    --多字段重复记录中最小的rowid
    select min(rowid) from tablename 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 '%村'
  • 阅读300
  • 评论 1
王奕凡 主要是学会使用sql中的having 关键词。这些东西在面试中也是经常被问到的。
2016/11/19 16:07:40 回复

最专业的职业问答社交网站

Copyright@2016 www.zai30.com 版权所有 鄂ICP备15023318号