MySQL子查询练习第20题宋红康讲错了

视频p48 子查询练习2
MySQL子查询练习第20题宋红康讲错了
查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
宋老师讲的

SELECT country_id
FROM locations l
WHERE 2 < (
	   SELECT COUNT(*)
	   FROM departments d
	   WHERE l.`location_id` = d.`location_id`
	 );
结果us #这里只是碰巧us是对的

自己数数，uk有两个部门，如果把条件改成“大于1”个部门，

SELECT country_id
FROM locations l
WHERE 1 < (#改成大于1
	   SELECT COUNT(*)
	   FROM departments d
	   WHERE l.`location_id` = d.`location_id`
	 );
显示us  #结果是不显示uk的 证明这个做法有问题

这题宋红康讲错了。因为locations并不是和国家编号一一对应的。
下面是自己想的做法

SELECT DISTINCT country_id
FROM locations l
WHERE 2 < (
SELECT COUNT(d.department_id)
FROM departments d
WHERE d.location_id IN
  (SELECT location_id FROM locations WHERE country_id=l.country_id)
 );
 显示us，uk

当然也可以用连接来做

SELECT country_id
FROM locations l,departments d
WHERE l.location_id=d.location_id
GROUP BY country_id
HAVING COUNT(department_id)>2