如何使用Recursive joins统计递归区域表各层级的城市总数

你需要用递归公共表表达式（CTE）先构建所有区域与其各级子区域的映射关系，再关联城市表统计总数，主流关系型数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11g R2+）都支持该语法，以下是可直接运行的实现代码：

WITH RECURSIVE RegionHierarchy AS (
    -- 锚点节点：以每个区域自身作为根节点初始化
    SELECT Id AS RootRegionId, Id AS SubRegionId
    FROM Regions
    UNION ALL
    -- 递归逻辑：遍历根节点下的所有层级子区域
    SELECT rh.RootRegionId, r.Id AS SubRegionId
    FROM RegionHierarchy rh
    JOIN Regions r ON rh.SubRegionId = r.ParentId
)
SELECT r.Name, COUNT(c.Id) AS CityCount
FROM RegionHierarchy rh
-- 关联区域表获取根区域名称
JOIN Regions r ON rh.RootRegionId = r.Id
-- 关联城市表，所有子区域对应的城市都归属到对应根节点统计
JOIN Cities c ON rh.SubRegionId = c.RegionId
GROUP BY r.Id, r.Name
-- 原查询的过滤条件，不需要可直接删除
HAVING COUNT(c.Id) > 1;

逻辑说明 #

1. 递归CTERegionHierarchy会生成全量的「根区域-所有下属子区域」映射，以你提供的示例数据为例：
   • EU（Id=1）对应的SubRegionId会包含1、2、3
   • Germany（Id=2）对应的SubRegionId仅包含2
   • France（Id=3）对应的SubRegionId仅包含3
2. 关联城市表统计时，所有子区域关联的城市都会被归集到对应的根区域下，最终输出结果和你预期完全一致：EU对应4个、Germany对应2个、France对应2个。

如果你使用的是不支持递归CTE的低版本数据库，可通过自定义函数遍历层级的方式实现相同逻辑，目前主流生产环境数据库版本都已支持递归CTE语法，是该场景下最简洁高效的实现方案。

内容的提问来源于stack exchange，提问作者rickythefox