SELECT * FROM CountryLanguage WHERE CountryCode IN (SELECT CountryCode FROM CountryLanguage WHERE IsOfficial='T' GROUP BY CountryCode HAVING COUNT(*) > 1); SELECT * FROM CountryLanguage WHERE CountryCode IN (SELECT CountryCode FROM (SELECT CountryCode, COUNT(*) AS cnt FROM CountryLanguage WHERE IsOfficial='T' GROUP BY CountryCode) AS tmp WHERE cnt>1);
WHERE exists
(
SELECT * FROM CountryLanguage
WHERE IsOfficial='T' GROUP BY CountryCode HAVING COUNT(*) > 1
); 为什么不适用exists呢,这个比in要效率高。具体效率情况你可以查看执行计划
第一种根据CountryCode分组后直接过滤掉不止一个的结果
第二种根据CountryCode分组后,将相同的个数,放在一个子查询的结果表中,在从这个表过滤
个人觉得第一种效率快
我发错分类,我用的是MySQL