22,210
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
CREATE TABLE #T(id INT,UserName NVARCHAR(100),Logintime DATETIME)
Insert #T
select 1,N'cowbo','2017-1-1 10:50' union all
select 2,N'cowbo','2017-2-1 10:50' union all
select 3,N'cowbo','2017-3-1 10:50' union all
select 4,N'hackc','2017-3-2 10:50'
Go
--测试数据结束
;WITH tempa AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY UserName ORDER BY id) AS num FROM #T
)
SELECT ( CASE WHEN tempa.num = 1 THEN id
ELSE NULL
END ) AS id ,
( CASE WHEN tempa.num = 1 THEN tempa.UserName
ELSE NULL
END ) AS UserName ,
tempa.Logintime
FROM tempa;
UPDATE T2 SET ID='',UserName='' FROM
(
(SELECT T1.*,ROW_NUMBER()OVER(PARTITION BY UserName,ORDER BY Logintime) AS RN) T2
WHERE RN>1