分组查询的sql

有个需求是这样的：大区和省份是一对多的关系。那么肯定在省份表中，有个逻辑外键叫大区id。这时候，我想看看大区的名称、属性、负责人等信息，还想看看大区下的省份数量。如果大区的表为area，省份表为province。
假设表的内容是这样：

image.png

可以得出sql语句如下：

--  犯懒没加多余字段
select area.id,area.name,count(province.id)
from area left join province on area.id=province.areaId 
group by area.id

然而得到的结果确是这样

image.png
可以看到，江北区是没有的
如果想要去掉，只需要添加一句：where province.areaId is not null
那么这时候，还想要province的id，然后做下一步的处理，该咋办呢。
用到了一个函数是这样的

select area.id,area.name,count(province.id),group_concat(province.id) 
from area left join province on area.id=province.areaId 
where province.areaId is not null
group by area.id

最终能得到的结果就是

image.png

那么再深入一点，是都统计出来了。但是我还想筛选一下，看汉东省所属的大区，和这个大区下的所有省市，怎么办呢？
在sql优化的时候，有个原则叫小表驱动大表，而使用的关键字叫做exists，能不能利用一下呢？
然后就写出了这样的语句

select area.id,area.name,count(province.id),group_concat(province.id) 
from area left join province on area.id=province.areaId 
where province.areaId is not null
and exists (select 1 from province p where p.areaId=area.id and p.id=5)
group by area.id

可以先想象，有一个连接的大长表，就像上图那样。那么重点就是筛选，筛选什么？筛选包含汉东省的区域，并且这个区域就是大长表的id(area.id)。所以就是如果有一条记录证明区域和省份是有联系的，那就应该保存下来这个大长表的记录。exists中的语句可以返回任意结果，0，1，p.id……
那么这里可以再次使用left join，而不是exists达到目的么？ maybe ^.