[２００分]如何用一条SQL 语句删除表中重复的记录，但要保留一条，具体如下 ：急等！！！！！！！！！(在线等待)

[zsq666]

在emply表中有（id,name,sex）字段
　记录（'001','张三','男'）重复有10条
　记录（'002','李死',''）重复有８条
用一条SQL 语句删除表中重复的记录，但要保留一条！

[zsq666]

up

[zsq666]

up

[xluzhong]

alter table emplay sid int identity(1,1)
go
delete a
from emplay a where id not in (select top 1 sid form emplay where id=a.id order by sid)
go
alter table emplay dorp column sid

[xluzhong]

create table a(id nvarchar(10),name nvarchar(10),sex nvarchar(10))
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''

select distinct * into #t from a
truncate table a

insert into a select * from #t

select * from a

drop table a,#t

[xluzhong]

create table a(id nvarchar(10),name nvarchar(10),sex nvarchar(10))
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
insert into a select '001','张三','男'
insert into a select '002','李死',''
go
alter table a add sid int identity(1,1)
go
delete a from a t where sid not in (select top 1 sid from a where id=t.id order by sid)
go
alter table a drop column sid
go

select * from a

drop table a

[paoluo]

在emply表中有（id,name,sex）字段
　记录（'001','张三','男'）重复有10条
　记录（'002','李死',''）重复有８条
用一条SQL 语句删除表中重复的记录，但要保留一条！
------------------------------------------------
常用的方法：借用临时表
Select Distinct * Into #T from emply
Delete from emply
Insert emply Select * from #T
Drop table #T

[net_cavalier]

Oracle 中可用：
delete from emply a where rowid > (select min(rowid) from emply where id=a.id and name=a.name and sex =a.sex)

Sql server中用一条语句难办，估计这种要求只有答题时才有，上面可应付了

[mengzulin]

begin tran
select DISTINCT [id],[name],[sex] into #temp from emply
TRUNCATE TABLE emply
INSERT INTO emply([id],[name],[sex])
select [id],[name],[sex] from #temp
commit tran