在MySQL中使用HAVING

前端之家收集整理的这篇文章主要介绍了在MySQL中使用HAVING前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

我有一张桌子,我需要从中选择所有名字不是唯一的人,并且只有在名字相似的人中,所有人都有不同的姓氏时才应该选择该集合.

例:

@H_301_7@FirstN LastN Bill Clinton Bill Cosby Bill Maher Elvis Presley Elvis Presley Largo Winch

我想获得

@H_301_7@FirstN LastN Bill Clinton

要么

@H_301_7@FirstN LastN Bill Clinton Bill Cosby Bill Maher

我尝试了这个,但它没有返回我想要的东西.

@H_301_7@SELECT * FROM Ids GROUP BY FirstN,LastN HAVING (COUNT(FirstN)>1 AND COUNT(LastN)=1))

[在Aleandre P. Lavasseur评论之后编辑我的帖子]

最佳答案
@H_301_7@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

如果MysqL不支持WITH,那么内部查询

@H_301_7@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

猜你在找的MySQL相关文章