SQLServer如何去掉部分字段重复数据？
需求：某个表的数据，希望得到手机号去重复之后的记录，重复取时间较早的数据……
-
--0. 定义表变量及初始数据
-
set nocount on
-
declare @t table(
-
mobile varchar(11),
-
loadTime datetime
-
)
-
--1 单条
-
--2,3 两条，号码和时间完全相同
-
--4,5 两条，号码同，时间不同
-
insert into @t (mobile, loadTime) values('13212345670','2014-04-05')
-
insert into @t (mobile, loadTime) values('13212345671','2014-03-02')
-
insert into @t (mobile, loadTime) values('13212345671','2014-03-02')
-
insert into @t (mobile, loadTime) values('13212345672','2014-02-03')
-
insert into @t (mobile, loadTime) values('13212345672','2014-03-04')
-
-
--1. row_number 同一号码，无论时间是否相同 rid都不会重复
-
select row_number() over(partition by mobile order by loadtime) as rid, * from @t ORDER BY mobile,loadTime
-
/*
-
rid mobile loadTime
-
-------------------- ----------- -----------------------
-
1 13212345670 2014-04-05 00:00:00.000
-
1 13212345671 2014-03-02 00:00:00.000
-
2 13212345671 2014-03-02 00:00:00.000
-
1 13212345672 2014-02-03 00:00:00.000
-
2 13212345672 2014-03-04 00:00:00.000
-
*/
-
--2. rank 同一号码, 同一时间 rid会重复; 同一号码 不同时间 rid 不会重复
-
select rank() over(partition by mobile order by loadtime) as rid, * from @t ORDER BY mobile,loadTime
-
/*
-
rid mobile loadTime
-
-------------------- ----------- -----------------------
-
1 13212345670 2014-04-05 00:00:00.000
-
1 13212345671 2014-03-02 00:00:00.000
-
1 13212345671 2014-03-02 00:00:00.000
-
1 13212345672 2014-02-03 00:00:00.000
-
2 13212345672 2014-03-04 00:00:00.000
-
*/
-
--3. 只能选择 row_number 来去重复……
-
;with t as (
-
select row_number() over(partition by mobile order by mobile,loadtime) as rid, * from @t
-
)
-
select * from t where rid=1 ORDER BY mobile,loadTime
-
/*
-
rid mobile loadTime
-
-------------------- ----------- -----------------------
-
1 13212345670 2014-04-05 00:00:00.000
-
1 13212345671 2014-03-02 00:00:00.000
-
1 13212345672 2014-02-03 00:00:00.000
-
*/
-
