create table #t(tag int, _date datetime) insert #t select 1, '2006-06-07' union all select 1,'2007-02-03' union all select 1,'2007-02-04' union all select 2,'2007-02-03'delete from #t where convert(varchar,tag) + '_' + convert(varchar,_date ,120) not in (select convert(varchar,tag) + '_' + convert(varchar,max(_date),120) from #t group by tag)select * from #t/* tag _date ----------- ------------------------------------------------------ 1 2007-02-04 00:00:00.000 2 2007-02-03 00:00:00.000 */drop table #t
select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by ID,autoID select * from #Tmp where autoID in(select autoID from #tmp2) 最后一个select即得到了ID,Tag不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)
insert #t select 1, '2006-06-07'
union all select 1,'2007-02-03'
union all select 1,'2007-02-04'
union all select 2,'2007-02-03'delete from #t
where convert(varchar,tag) + '_' + convert(varchar,_date ,120)
not in (select convert(varchar,tag) + '_' + convert(varchar,max(_date),120) from #t group by tag)select * from #t/*
tag _date
----------- ------------------------------------------------------
1 2007-02-04 00:00:00.000
2 2007-02-03 00:00:00.000
*/drop table #t
select min(autoID) as autoID into #Tmp2 from #Tmp group by ID,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了ID,Tag不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)