要用临时表 select identity(int,1,1) as id,* into #T from 表 delete from #T where id not in ( select max(id) from #T group by 字段1,字段2,字段3 )要用临时表 select identity(int,1,1) as id,* into #T from 表 delete from #T where id not in ( select max(id) from #T group by 字段1,字段2,字段3 ) delete from 表 insert into 表 (字段1,字段2,字段3 ) select 字段1,字段2,字段3 from #Tdelete from 表 insert into 表 (字段1,字段2,字段3 ) select 字段1,字段2,字段3 from #T
select DISTINCT * into #temp from table delete from tablename insert into tablename select #temp delete from #temp
晕,贴重复了,改改。要用临时表 select identity(int,1,1) as id,* into #T from 表 delete from #T where id not in ( select max(id) from #T group by 字段1,字段2,字段3 ) delete from 表 insert into 表 (字段1,字段2,字段3 ) select 字段1,字段2,字段3 from #T
select DISTINCT * into #temp from table delete from tablename insert into tablename select * from #temp delete from #temp
或者 Select Distinct * Into #T from 表 Delete from 表 Insert Into 表 Select * from #T Drop table #T
参考一下这个,以前别人给我回答的! 如果有主键id 则:delete from tablename where id not in(select min(id) from tablename group by col1,col2..) 如果没有id 则:alter table tablename add id int identity(1,1) delete from tablename where id not in(select min(id) from tablename group by col1,col2..)
那你的表中的关键字是什么??看看有没有别的方法。其实这个方法已经很简洁了。Select Distinct * Into #T from 表 Delete from 表 Insert Into 表 Select * from #T Drop table #T
alter table ttt add id int identity(1,1) delete from ttt where id not in(select min(id) from ttt group by 字段一,字段二,字段三) alter table ttt drop column id
to paoluo(一天到晚游泳的鱼) : 谢谢先,我要的不是简洁,而是效率,我用临时表试了一下,386条记录耗时42秒,太慢了(P4 2.0G 256M)。相比来说建一个ID字段有选择的删除要好一些了。
select identity(int,1,1) as id,* into #T from 表
delete from #T
where id not in
(
select max(id) from #T group by 字段1,字段2,字段3
)要用临时表
select identity(int,1,1) as id,* into #T from 表
delete from #T
where id not in
(
select max(id) from #T group by 字段1,字段2,字段3
)
delete from 表
insert into 表 (字段1,字段2,字段3 ) select 字段1,字段2,字段3 from #Tdelete from 表
insert into 表 (字段1,字段2,字段3 ) select 字段1,字段2,字段3 from #T
delete from tablename
insert into tablename select #temp
delete from #temp
select identity(int,1,1) as id,* into #T from 表
delete from #T
where id not in
(
select max(id) from #T group by 字段1,字段2,字段3
)
delete from 表
insert into 表 (字段1,字段2,字段3 ) select 字段1,字段2,字段3 from #T
delete from tablename
insert into tablename select * from #temp
delete from #temp
Select Distinct * Into #T from 表
Delete from 表
Insert Into 表 Select * from #T
Drop table #T
如果有主键id
则:delete from tablename where id not in(select min(id) from tablename group by col1,col2..)
如果没有id
则:alter table tablename add id int identity(1,1)
delete from tablename where id not in(select min(id) from tablename group by col1,col2..)
Delete from 表
Insert Into 表 Select * from #T
Drop table #T
delete from ttt where id not in(select min(id) from ttt group by 字段一,字段二,字段三)
alter table ttt drop column id
谢谢先,我要的不是简洁,而是效率,我用临时表试了一下,386条记录耗时42秒,太慢了(P4 2.0G 256M)。相比来说建一个ID字段有选择的删除要好一些了。