use tb

go

set nocount on

create table #Tmp --创建临时表#Tmp

(

FCarNumber VARCHAR(50) ,

maxid int ,

num int

);

insert into #Tmp(FCarNumber,num,maxid)

select FCarNumber,COUNT(0) as num,Max(PID) as maxid from T_GPSCar with (nolock) group by FCarNumber having count(0)>1

delete T_GPSCar from

#Tmp p where p.FCarNumber=T_GPSCar.FCarNumber and T_GPSCar.PID<>p.maxid

Select COUNT(0) as num from #Tmp --查询临时表的数据

truncate table #Tmp --清空临时表的所有数据和约束

drop table #Tmp

set nocount off