表结构

iot_street_road表：

iot_street_staff_road表：

 

iot_street_tree表:

想得到结果：

road_name	staff_count	tree_count
街道名	工人数量	苗木数量

 

实现方法：

把表一和表二、表一和表三分别按road_id聚合，得到的结果合并。

不能三张表一起聚合。

 

第一步：iot_street_road表和iot_street_staff_road表聚合

select road_name, count(sroad.sstaff_id) as staff_count 
from iot_street_road as road
right join iot_street_staff_road as sroad 
on road.id=sroad.sroad_id
group by road.id

第二步：iot_street_road表和iot_street_tree表聚合

select road_name, count(stree.id) as tree_count 
from iot_street_road as road 
right join iot_street_tree as stree 
on road.id=stree.road_id 
group by road.id

 

第三步：将两个聚合结果合并

select a1.*, a2.tree_count from (sql语句1) a1 inner join (sql语句2) a2 on a1.road_id=a2.road_id