表如下
------abc
id c1 c2 c3 time
293356891874800 000004 0004 WD 2009-04-21 00:36:54.000
254058747623323 000004 0004 WD 2008-03-20 14:44:27.000
298021732124469 000008 YY1137 WD 2009-06-24 16:35:44.000
254058747621378 000008 YY1137 WD 2008-03-20 14:44:27.000
261651864320429 000011 0011 WD 2009-11-03 00:35:29.000
305797784624233 000011 0011 WD 2009-11-03 00:35:29.000
293356895415870 000018 0018 WD 2009-04-24 00:38:56.000
254058747621021 000018 0018 WD 2008-03-20 14:44:27.000
295754107138089 000020 0020 WD 2009-05-19 00:39:47.000
254058747623028 000020 0020 WD 2008-03-20 14:44:27.000
根据c1,c2,c3相同的留time最新的。
------abc
id c1 c2 c3 time
293356891874800 000004 0004 WD 2009-04-21 00:36:54.000
254058747623323 000004 0004 WD 2008-03-20 14:44:27.000
298021732124469 000008 YY1137 WD 2009-06-24 16:35:44.000
254058747621378 000008 YY1137 WD 2008-03-20 14:44:27.000
261651864320429 000011 0011 WD 2009-11-03 00:35:29.000
305797784624233 000011 0011 WD 2009-11-03 00:35:29.000
293356895415870 000018 0018 WD 2009-04-24 00:38:56.000
254058747621021 000018 0018 WD 2008-03-20 14:44:27.000
295754107138089 000020 0020 WD 2009-05-19 00:39:47.000
254058747623028 000020 0020 WD 2008-03-20 14:44:27.000
根据c1,c2,c3相同的留time最新的。
from abc t
where
not exists(select 1 from abc where c1=t.c1 and c2=t.c2 and c3=t.c3 and [time]>t.[time])
WHERE TIME=(SELECT MAX(TIME) FROM TB WHERE C1=T.C1 AND C2=T.C2 AND C3=T.C3)
*
from
abc t
where
not exists(select 1 from abc where c1=t.c1 and c2=t.c2 and c3=t.c3 and [time]>t.[time])
select *,ROW_NUMBER()over(partition by t1,t2,t3 order by time desc) as row from abc where t1=t2 and t2=t3
)a where a.row=1
select *,ROW_NUMBER()over(partition by t1,t2,t3 order by time desc) as row from abc where t1=t2 and t2=t3
)a where a.row=1