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少的那条
语句怎么写
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少的那条
语句怎么写
WHERE NOT EXISTS
(
SELECT 1
FROM TB B
WHERE b.DSN = a.DSN
AND b.PID = a.PID
AND b.[TIME] < a.[TIME]
)
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/*
DSN NAME PID NI [TIME]
11 A1 113 74 20130730085133
23 B4 45 7 20130720085123
92 B5 76 5 20130720015115
*/
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
;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