您现在的位置:主页 > 技术中心 > 数据库技术 > Sqlserver

SQL查询重复记录,删除重复记录

来源:未知 责任编辑:责任编辑 发表时间:2014-05-26 11:01 点击:

1、查找表中多余的重复记录,重复记录是根据单个字段(DocId)来判断
select * from TableName
where DocId in (select DocId from TableName group by DocId having count(DocId) > 1)


 例二:
select * from TableName
where UserName in (select UserName from TableName group by TableName having count(TableName) > 1 )

 可以查出表中UserName相同的记录

2、删除表中多余的重复记录,重复记录是根据单个字段(DocId)来判断,只留有DocId最小的记录
delete from TableName
where DocId in (select  DocId from TableName group by DocId  having count(DocId) > 1)
and DocId not in (select min(DocId) from  TableName group by DocId having count(DocId)>1)


3、查找表中多余的重复记录(多个字段)
select * from TableName T
where (T.DocId+T.ColumnName) in (select DocId+ColumnName from TableName group by DocId,ColumnName having count(*) > 1)

--前提是“DocId,ColumnName”为字符型,否则需要转换 即:convert(varchar,DocId)


4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from TableName T
where (T.ColumnA+T.ColumnB) in (select ColumnA+ColumnB from TableName group by ColumnA,ColumnB having count(*) > 1)
and DocId not in (select min(DocId) from TableName group by ColumnA,ColumnB having count(*)>1)

--前提是“DocId,ColumnName”为字符型,否则需要转换 即:convert(varchar,DocId)


5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from TableName T
where (T.ColumnA+T.ColumnB) in (select ColumnA+ColumnB from TableName group by ColumnA,ColumnB having count(*) > 1)
and DocId not in (select min(DocId) from TableName group by ColumnA,ColumnB having count(*)>1)

--前提是“DocId,ColumnName”为字符型,否则需要转换 即:convert(varchar,DocId)



作者 小阿
    发表评论
    请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
    评价:
    表情:
    用户名: 验证码:点击我更换图片

    推荐热点

    • sql常见面试题
    • SQL SERVER 2005性能之跟踪
    • SQL编程(一)
    • LINUX上RMAN自动备份脚本
    • sql server面试题
    • 浅谈SQL Server中的事务日志(三)----在简单恢复模式
    • SQL小技巧系列 --- 行转列合并
    • 如何将多个SQL查询统计结果一次显示出来
    • sql server 列转行
    ?? - ?? - ÝřŝžľŘÝź - TAGąęÇŠ - RSSśŠÔÄ - ??
    Copyright © 2004-2024 上海卓卓网络科技有限公司