count(distinct xxx) 和 group by 做去重隐藏的坑

不说废话，直接上代码。

以下查询是基于mysql自带的country,city,countrylanguage三个样例表做的：

select count(*) from country
union all 
SELECT count(distinct Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2)
FROM world.country
union all 
select count(*) from 
(
SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2
FROM world.country
group by Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2
) a;

查询结果如下：

发现使用count(distinct xxx)后的结果居然和group by 去重的数据不一致；

不用怀疑，就是NULL引起的࿰