sqlserver中重复,sqlserver查询重复数据

如何使用sql语句在sqlserver中删除重复数据

题主可参考下列例句:

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：主机域名、网络空间、营销软件、网站建设、沙湾网站维护、网站推广。

删除表t1字段col1有重复的记录

delete from t1 where exists

(select 1 from (select col1 from t1 group by col1 having count(1)1) t where t.col1=t1.col1);

如果希望对于有重复的记录希望保留其中一条记录而不是全部删除，则可以运行下列语句，前提是数据表必须含有自增id列。

delete from t1 where exists

(select 1 from (select col1,max(id) as id from t1 group by col1 having count(1)1) t where t.col1=t1.col1 and t.idt1.id);

找到最大的rowid即可。

Sql代码：

alter proc getNotDupData

--clear temp table

delete ODS.dbo.Agent

delete from stage.dbo.tmpDup

delete from stage.dbo.tmpRowNo

delete from stage.dbo.tmpMaxRowNo

--create dup table

insert into stage.dbo.tmpDup

select distinct AgentLogin,AgentSurName,AgentGivenName from stage.dbo.dAgentPerformanceStat

where AgentSurname is not null and agentlogin like '3%' order by AgentLogin

--add rowNo

insert into tmpRowNo

select *,ROW_NUMBER()over(order by AgentLogin) as rowno from tmpDup

--get max rowno

insert into stage.dbo.tmpMaxRowNo

select max(rowno) as 'rowno' from stage.dbo.tmpRowNo group by AgentLogin having count(*)1

--remove max rowno

delete from stage.dbo.tmpRowNo where rowno in (select * from stage.dbo.tmpMaxRowNo)

--insert into ods

insert into ODS.dbo.Agent select AgentLogin,AgentSurName,AgentGivenName from stage.dbo.tmpRowNo

select distinct * into #tmp from s2

drop table s2

select * into s2 from #tmp

drop table #tmp

利用临时表删除重复数据

文章题目：sqlserver中重复,sqlserver查询重复数据
转载源于：http://jkwzsj.com/article/dseoshj.html