如何用SQL语句把重复的记录删除，只剩下一条记录，谢谢！

[stone565]

各位前辈，如题，谢谢！

[internetcsdn]

ha..................

[szwelcome]

select distinct field1,field2 into temptable from table1
drop table table1
select distinct field1,field2 into table1 from temptable
drop table temptable

[jackting]

select distinct *　 into #temptable from table1
TRUNCATE TABLE table1
insert into table1 select * from #temptable

[sfb]

不同的数据库有不同的语句方法，并不是很通用
oracle可以用rowid

这是一个简单的问题，建议提问前搜索一下网络，上面很多

[wxyq]

delete from table where field1 in (select * from table where field1 not in (select distinct field1 from table))

[torrent2008]

--如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
/*
这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
*/
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
/*
最后一个select即得到了Name，Address不重复的结果集
（但多了一个autoID字段，实际--写时可以写在select子句中省去此列）
*/

[xkforever]

表中可以有重复的元组吗？？/

[internetcsdn]

http://community.csdn.net/Expert/topic/3002/3002141.xml?temp=.9970819

[internetcsdn]

一样的问题,应该可以帮到你

[netcoder]

alter table 表
add ID int identity(1,1)
go
delete from 表
where
ID not in (select max(ID) from 表 group by 字段1,字段2, 字段3,字段4,……)
go
alter table 表
drop column ID

[guhguh]

create table t1(id int identity(0,1),a varchar(10),b varchar(10))
insert t1(a,b)
select 'aa','bb'
union all select 'a1','bgb'
union all select 'aa','bb'
union all select 'a2','bb'
union all select 'aa3','beeb'
union all select 'aa','bb'
union all select 'a2','bb'



delete t1
where id not in
(select min(id) as id from t1 group by a,b)----------------a,b重复.-----------

select * from t1

drop table t1

/*
id a b
------------------
0 aa bb
1 a1 bgb
3 a2 bb
4 aa3 beeb

*/