查找重复数据?

[mrking]

现有一表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

[dawugui]

--如果是查询
select distinct name from temp

[dawugui]

--如果是删除,将查询出来的数据放一临时表,清空原来的表,然后将数据从临时表中导回来
select distinct name into test from temp
delete from temp
insert into temp select * from test

[lass_name]

楼上正解啊！！

[vchao13]

楼上正解。。。

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

[mrking]

select distinct name from temp
这句没有查找出重复数据的功能,有没有其他的办法啊

[fcuandy]

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 #

[IT孤儿]

--查找所有重复数据
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

[nobody@noone]

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

[yuemb369]

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

[wuya8115]

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

[SQLnewlearner]

delete from table where name in(select name from table group by name having count(name)>1)

[ShaRoom]

不含distinct的sql.
楼上的很好:
delete from table where name in(select name from table group by name having count(name) >1).

[gbys]

mark