题目。
一开始的思路:
SELECT
countries_regions.continent,
COUNT(cities.name) as num
FROM cities
INNER JOIN countries_regions
ON countries_regions.code=cities.country
WHERE cities.latitude < 0
GROUP BY countries_regions.continent
发现缺少NULL 项,七大洲只有四个。
后来尝试SELECT(select。。。 group by)提示返回多行
因为上面子查询中 group by 以后返回的时四行,作为子查询,只可以返回一行,所以不行
查过资料后知道,WHERE 中的限制条件可以丢到JOIN中,所以有:
SELECT
countries_regions.continent,
COUNT(cities.name) as num
FROM cities
RIGHT JOIN countries_regions
ON countries_regions.code=cities.country
AND cities.latitude < 0
GROUP BY countries_regions.continent
;
得到正确答案
附一个关系图