sql如何删除部分字段重复的数据

来源：未知责任编辑：责任编辑发表时间:2014-01-25 11:36　点击:次

思路：找到最大的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

作者 wandejun1012