查找重复数据?解决办法

查找重复数据?
现有一表temp其中一列如下:
 name
A-H1-047
A-H1-002
A-H1-003
A-H1-047
A-H1-047
A-H1-047

想查找出name列中有重复的数据A-H1-047并删除该行,得到如下:

 name
A-H1-047
A-H1-002
A-H1-003

[解决办法]

SQL code

--如果是查询select distinct name from temp
[解决办法]
SQL code
--如果是删除,将查询出来的数据放一临时表,清空原来的表,然后将数据从临时表中导回来select distinct name into test from tempdelete from tempinsert into temp select * from test
[解决办法]
楼上正解。。。

select distinct name into test from temp
truncate table temp
insert into temp select * from test

[解决办法]
1,若查找含有重复行的name
select name from tb group by name having count(*)>1

2,若查找重复name的记录,只保留一条
select distinct name into # name from tb
truncate table tb
insert tb select name from #
drop table #
[解决办法]
--查找所有重复数据
select * from temp where name in (select name from temp group by name having count(name)>1)
要删除就是
select distinct name into test from temp
delete from temp
insert into temp select * from test


[解决办法]
不是先group by 有用的字段然后用not in delete么
我想这个表应该有个主键的吧，我用id代替
delete from temp where id not in(select max(id),name from temp group by name )
[解决办法]

select 'a' as [name] into #temp
insert into #temp  
select 'b'
union all  
select 'b'
union all  
select 'c'


delete from #temp where name in (select name from #temp group by name having count(name)>1)   --把这句中的#temp换成你自己的temp就可以了，其余的语句为测试语句  

select * from #temp
[解决办法]
select distinct name into test from temp
truncate table temp
insert into temp select * from test
[解决办法]
delete from table where name in(select name from table group by name having count(name)>1)
[解决办法]
不含distinct的sql.
楼上的很好:
delete from table where name in(select name from table group by name having count(name) >1).