查找相同字段的sql语句怎么写？

[xl_xl]

表Information中有四个字段：ID,Name,Sex,Position
ID为主键，现在我想查找姓名一样的记录，sql语句应该怎么写，谢谢。

[昵称被占用了]

select * from Information
where exists (
select Name from Information where name=a.name group by name having count(*)>1)

[net_steven]

稍做补充：
select * from Information
where exists (
select Name from Information group by name having count(*)>1)
order by name

[昵称被占用了]

狼兄，你把那个WHERE去掉了就不对了！

[xl_xl]

如果要删除其中的一个呢？

[xl_xl]

如果要删除其中相同记录的一条呢？

[warning]

稍作补充：
select * from Information a
where exists (
select Name from Information where name=a.name group by name having count(*)>1)

[x_zing]

删除哪一个啊？

[xl_xl]

查找出相同姓名的记录后，删除其中的一个，我的意思是姓名相同的记录保留一条。

[昵称被占用了]

delete Information
from Information a,
(
select max(id) as id,Name from Information
group by name having count(*)>1
) as b
where a.id<>b.id
and a.name=b.name

[昵称被占用了]

以上留ID最大的一条。

[SE1]

稍作补充：
select *
from Information
where Name in (
select Name from Information group by name having count(*)>1)

效率更高!

[N_chow]

Select A.* From Information A INNER JOIN (Select name from Information group by name having count(*)>1) B
ON A.name=b.Name

[net_steven]

to haiwer:
对不起，我写错了，本意是要象SE1() 写的。

[xl_xl]

to haiwer(海阔天空--回复语句只对MSSQL有效):
报：select附件有错。

[xl_xl]

先把分给了再说