关于两个重复字段的问题
DSN NAME PID NI TIME
11 A1 113 74 20130730085133
11 B3 113 62 20140330095121
23 B4 4 5 7 20130720085123
92 B5 7 6 5 20130720015115
结果
DSN NAME PID NI TIME
11 A1 113 74 20130730085133
23 B4 4 5 7 20130720085123
92 B5 7 6 5 20130720015115
DSN,PID 重复只留一条，取TIME少的那条
语句怎么写
[解决办法]
SELECT * FROM TB A
WHERE NOT EXISTS
(
SELECT 1
FROM TB B
WHERE b.DSN = a.DSN
AND b.PID = a.PID
AND b.[TIME] < a.[TIME]
)
create table #tb(DSN varchar(2), NAME varchar(2), PID int,NI int,[TIME] varchar(20))
insert into #tb
select 11,'A1',113,74,'20130730085133'
union all select 11,'B3',113,62,'20140330095121'
union all select 23,'B4',45,7,'20130720085123'
union all select 92,'B5',76,5,'20130720015115'
select DSN,NAME,PID,NI,[TIME]
from (select *,ROW_NUMBER() over(partition by DSN order by PID ) rn from #tb)t
where rn=1
/*
DSNNAMEPIDNI[TIME]
11A11137420130730085133
23B445720130720085123
92B576520130720015115
*/
--方法2
SELECT B.*
FROM (SELECT DISTINCT DSN, PID FROM tb) a
CROSS APPLY
(SELECT TOP(1) * FROM TB m WHERE m.DSN=a.DSN AND m.PID=a.PID ORDER BY m.[TIME]) b
--方法3
;WITH cte AS
(
SELECT rowid =ROW_NUMBER() OVER(PARTITION BY DSN, PID ORDER BY [time]), *
FROM TB
)
SELECT DSN,NAME,PID,NI,[TIME]
FROM cte
WHERE rowid = 1
