区域-区域-兴趣点查询优化

Question

我的数据库结构如下：

每个地区(CountryStates)可以有多个城市(Areas)。每个Area可以有许多旅游景点或兴趣点(POIs)。我想要得到每个地区有多少地区和景点的列表。如果没有，我想显示0。

这是我的问题：

SELECT Tab1.Reg AS Reg, CountAreas, CountPois
FROM
(SELECT 
        c.Name AS Reg, 
        COUNT(a.Id) AS CountAreas
FROM  
       CountryStates as c LEFT JOIN
       Areas AS a ON a.CountryStates_Id = c.Id
GROUP BY c.Name 
) as Tab1 left join
(SELECT 
        c1.Name AS Reg, 
        COUNT(p.Id) AS CountPois
FROM  
       CountryStates as c1 LEFT JOIN
       Areas AS a ON a.CountryStates_Id = c1.Id LEFT JOIN
       POIs AS p ON a.Id = p.Areas_Id
GROUP BY c1.Name 

) as Tab2 on Tab1.Reg = Tab2.Reg

如何在一个SELECT中执行此查询

Answer 1

此查询返回相同的结果：

SELECT c.Name AS Reg, 
        COUNT(DISTINCT(a.Id)) AS CountAreas,
        COUNT(p.Id) AS CountPois
FROM CountryStates as c 
    LEFT JOIN Areas as a ON a.CountryStates_Id = c.Id
    LEFT JOIN POIs AS p ON a.Id = p.Areas_Id
GROUP BY c.Name