怎样查找表里面相同的数据？并把不符合条件的删除？

[ywj99]

表：user
id name sex
1 a 0
2 b 0
3 c 0
4 b 1
5 e 1
6 c 1
7 a 1
8 f 0

怎样查找这个user表中name自相同的所有数据？
结果表应该是
id name sex
1 a 0
7 a 1
2 b 0
4 b 1
3 c 0
6 c 1

然后把找出的相同数据删除sex为0的所有记录
结果表应该是
id name sex
7 a 1
4 b 1
6 c 1


求教~~~~~~~~~~~~~~~~~~~~~~~~~

[hsj20041004]

delete from tablename where name in (select name from tablename group by name having count(*)>1)
and sex=0

[wwwwb]

SELECT a.id,a.name,a.sex FROM qq1 a INNER JOIN
(SELECT MAX(c.id) as id,c.name FROM qq1 c GROUP BY name HAVING COUNT(*)>=2) b ON a.id=b.id AND a.name=b.name order by a.name

[zlp321002]

--同意: hsj20041004(光芒)

[paoluo]

按照楼主要的结果，光芒少考虑了一步。


Delete from tablename
where (name in (select name from tablename group by name having count(1)>1) and sex=0 )
Or (Name Not In (select name from tablename group by name having count(1)>1))

[wangdehao]

---还是老鱼想的周全,光芒的可能是想select了
Delete from tablename
where (name in (select name from tablename group by name having count(1)>1) and sex=0 )
Or (Name In (select name from tablename group by name having count(1)=1))

[hsj20041004]

to:paoluo(一天到晚游泳的鱼)
你那样的话，不就把不重复的记录也删除了吗？？？？？？？？？
你的不对！

[hsj20041004]

怎样查找这个user表中name自相同的所有数据？
看楼主的意思吧。

[paoluo]

回复人： hsj20041004(光芒) ( ) 信誉：100 2005-07-01 10:32:00 得分: 0


to:paoluo(一天到晚游泳的鱼)
你那样的话，不就把不重复的记录也删除了吗？？？？？？？？？
你的不对！


－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
我没说表述清楚，我的意思是说按楼主最后显示的结果的话，就是我写的。

并不是说你的错了。

他最后显示的结果是没有不重复的记录的。

[feitianbianfu16]

delete from tablename where not(name in (select name from tablename group by name having count(*)>1)
and sex=1)