求一个SQL语句:查一个指定字段中的数据出现指定次数的记录

[ltolll]

如有两个表:
表A
字段:
name sex
john male
rose female

表B
字段:
name ... buy
john .... ineve
rose .... pc
rose .... dell
rose .... ibm
john .... micro

要求查出在表B中name字段出现指定次数的记录,比如在name中出现2次则查出:
name sex buy
john male ineve
john male micro

[siemems]

Select * from 表B
Where name in
(Select name from 表B Group by name Having Count(name)=2)

[tianma_66]

用一条SQL语句:
SELECT a.name, a.sex, b.buy
FROM a INNER JOIN
b ON a.name = b.name
WHERE (b.name IN
(SELECT name
FROM b
GROUP BY name
HAVING (COUNT(name) = 2)))