场景:一张注册表有很多重复的userid,现在要去除重复并保留最新注册时间
有两种方案:
方案一:
利用Row_Number 函数对数据分组排序 按照useridx分组,按wtime 降序来排序 然后只取排序中每组第一条数据
select id,useridx,userid,clientclass,channelclass,schannelclass,itime,wtime,uniquenum,param2 as param1
into #Mobile9158_Reg_Info
from (select * , ROW_NUMBER() OVER(PARTITION BY useridx ORDER BY wtime desc )as ss
from Mobile9158_Reg_Info(nolock))as tt where ss=1
方案二:
先用group by 选出注册最大时间,然后将其放置于where 语句中 ,该方案一定程度上会有重复
所以要用distinct 去重
select distinct a.useridx,a.wtime,a.channelclass,a.param2 as param1 into #Mobile9158_Reg_Info from Mobile9158_Reg_Info a(nolock)
where wtime=(
SELECT MAX(b.wtime) AS wtime from Mobile9158_Reg_Info b(nolock)
where b.wtime<@today and b.useridx=a.useridx
group by b.useridx
) order by wtime desc
总结:在千万量数据下,方案二比方案一时间要短很多