sql若何删除部份字段反复的数据[MSSQL防范]

　　思绪:找到最大的rowid便可.

　　Sql代码

　　alter proc getNotDupData

　　as

　　--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