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

思路：找到最大的rowid即可。

 

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

 

 

 

附上删除另外一个重复的例子，和上面的有点类似，但是用在不同的项目里的，放在这里加深一下印象：

 

select max(id) from T_CUST_CUSTINFO_12 where a627='yc201204-5'  group by a606 having count(*)>1