我有一张桌子,我需要从中选择所有名字不是唯一的人,并且只有在名字相似的人中,所有人都有不同的姓氏时才应该选择该集合.
例：
FirstN LastN
Bill Clinton
Bill Cosby
Bill Maher
Elvis Presley
Elvis Presley
Largo Winch
我想获得
FirstN LastN
Bill Clinton
要么
FirstN LastN
Bill Clinton
Bill Cosby
Bill Maher
我尝试了这个,但它没有返回我想要的东西.
SELECT * FROM Ids
GROUP BY FirstN,LastN
HAVING (COUNT(FirstN)>1 AND COUNT(LastN)=1))
[在Aleandre P. Lavasseur评论之后编辑我的帖子]
最佳答案
WITH duplicates AS (
SELECT firstn --,COUNT(*),COUNT(DISTINCT lastn)
FROM ids
GROUP BY firstn
HAVING COUNT(*) = COUNT(DISTINCT lastn)
AND COUNT(*) > 1
)
SELECT a.firstn,a.lastn
FROM ids a INNER JOIN duplicates b ON (a.firstn = b.firstn)
ORDER BY a.firstn,a.lastn
SELECT a.firstn,a.lastn
FROM ids a,(SELECT firstn --,COUNT(DISTINCT lastn)
FROM ids
GROUP BY firstn
HAVING COUNT(*) = COUNT(DISTINCT lastn)
AND COUNT(*) > 1
) b
WHERE a.firstn = b.firstn
ORDER BY a.firstn,a.lastn