请教这个SQL语句怎么写

[xbgs]

假设一个表中有一个字段不是主键，所以应该有重复的值，现在想写SQL语句找出所有重复的值，怎么查找？

[97866]

Select 字段 from 表 Group by 字段 HAVING Count(字段)>1

[xbgs]

还有下一步就是怎么样把重复的字段值只保留一个，把其他重复的记录全部删除？

[liuningxy]

select feilds,count(*) as 次数
from table
group by feilds
having count(*)>1

就可以显示出feilds字段均重复的字段信息和重复次数

[liuningxy]

select distinct feilds
into new_table
from old_table
where 1=1

drop table old_table