写一条sql语句去除一张表中的重复数据
如题。。。。。

但是要注意的是，不是仅仅显示不重复的数据，是发现有重复的数据删除一条重复的。

比如：

Username Department

Roy COM
Alwin UDDI
Roy COM

写一条sql语句删除roy --- com.

thanks a lot.

[解决办法]

SQL code

select distinct * into # from tbgotruncate table tbgoinsert tb select * from #godrop table #
[解决办法]
探讨

SQL codeselectdistinct*into #from tbgotruncatetable tbgoinsert tbselect*from #godroptable #

[解决办法]
探讨

SQL codeselectdistinct*into #from tbgotruncatetable tbgoinsert tbselect*from #godroptable #

[解决办法]
出现完全相同的?

select distinct * into tmp from tb
truncate table tb
insert into tb select * from tmp
[解决办法]
SQL code
create table test(username varchar(20),department varchar(20))insert testselect 'roy','com' union allselect 'Alwin','UUDI' union allselect 'roy','com' godelete test where username in (select username from test group by username having count(*)>1)select * from testusername             department           -------------------- -------------------- Alwin                UUDI（所影响的行数为 1 行）
[解决办法]
探讨

出现完全相同的?

 select distinct * into tmp from tb
 truncate table tb
 insert into tb select * from tmp

[解决办法]
SQL code
create table test(username varchar(20),department varchar(20))insert testselect 'roy','com' union allselect 'Alwin','UUDI' union allselect 'roy','com' go--drop table testdelete test where username in (select username from test group by username,department having count(*)>1)select * from testusername             department           -------------------- -------------------- Alwin                UUDI（所影响的行数为 1 行）
[解决办法]
探讨

SQL codeselectdistinct*into #from tbgotruncatetable tbgoinsert tbselect*from #godroptable #