delete from tableName a where not exists(select 1 from tableName where a.编号=编号 and a.日期<日期)
delete t from table t where 日期 =(select max(日期 ) from table where 编号=t.编号)
删除最小的 delete a from @T a where 日期=(select min(日期) from @T where a.编号=编号)
if object_id('tb') is not null drop table tb go create table tb([编号] varchar(10),[数量] int,[日期] varchar(20)) insert tb select '1',4,20090301001 union all select '1',4,20090301002 union all select '2',5,20090401001 union all select '2',5,20090401002 go delete t from tb t where not exists(select 1 from tb where 编号=t.编号 and 日期>t.日期) select * from tb /* 编号 数量 日期 ---------- ----------- -------------------- 1 4 20090301001 2 5 20090401001(2 行受影响) */
delete A where 日期 not in(select top 1 日期 from A t where t.编号=编号 order by newid())
select id,Count(id) from tab group by id having count(ID) >= 2 这个是查找重复的,你自己修改一下就可以了
delete a from @T a where 日期=(select min(日期) from @T where a.编号=编号)
go
create table tb([编号] varchar(10),[数量] int,[日期] varchar(20))
insert tb select '1',4,20090301001
union all select '1',4,20090301002
union all select '2',5,20090401001
union all select '2',5,20090401002
go
delete t from tb t where not exists(select 1 from tb where 编号=t.编号 and 日期>t.日期)
select * from tb
/*
编号 数量 日期
---------- ----------- --------------------
1 4 20090301001
2 5 20090401001(2 行受影响)
*/
where 日期 not in(select top 1 日期 from A t where t.编号=编号 order by newid())
这个是查找重复的,你自己修改一下就可以了