删除相同的数据如何写？

[hyit2004]

create table #test
(id int,
badge varchar(20),
num decimal(9,2)
)
insert into #test
select 1,0025,92.5
union all
select 2,0024,92.3
union all
select 3,0023,92.2
union all
select 3,0023,92.2
union all
select 4,0021,92.1
union all
select 5,0020,92.0
union all
select 6,0028,91.1

select * from #test
drop table #test

id badge num
----------- -------------------- -----------
1 25 92.50
2 24 92.30
3 23 92.20
3 23 92.20
4 21 92.10
5 20 92.00
6 28 91.10

其中ID是3的有两条相同的记录，我如果删除其中一条，变成
id badge num
----------- -------------------- -----------
1 25 92.50
2 24 92.30
3 23 92.20
4 21 92.10
5 20 92.00
6 28 91.10
此题之意是只有有两条及两条以上相同，只保留一条，建议只在＃test表里删除操作！望赐教！

[tangqijun199]

select * into #3 from #test where id=3

delete #test where id=3

insert into #test select top 1 * from #3

[xueguang]

select id,min(badge) badge,min(num) num into #1 from #test group by id
truncate table #test
insert #test select * from #1

[xueguang]

如果只是个别记录,也可以这样
set rowcount 1
delete #test where id=3
set rowcount 0

[geniusqing]

select * into #aa from #test group by id,badge,num
delete from #test
insert into #test
select * from #aa
select * from #test

[hyit2004]

这个重复数据不能确定是哪一个，我希望能把重复的筛选出来参数，但是原来数据里面还要保留一个！谢谢！

[vivianfdlpw]

select [a]=identity(int,1,1),* into # from #test
truncate table #test
insert #test
select id,badge,num from # t
where not exists(select 1 from # where id=t.id and a>t.a)


--查看
select * from #test

[Yisa]

这样就OK了.

select distinct * into #t from #test
go
truncate table #test
go
insert into #test select * from #t
go
select * from #test
go

[vivianfdlpw]

select id,[badge]=min(badge),num=min(num) into # from #test group by id
truncate table #test
insert #test select * from #

--查看
select * from #test

[hyit2004]

^_^ 以前我写过select就可以删除，关键是我想不起来，不管怎么样，谢谢大家