如何编写SQL递归查询树形层级数据表以获取指定国家或州下的所有城市

前提说明 #

假设你的层级数据表名为 geo_hierarchy，以下语句兼容MySQL 8.0+、PostgreSQL、SQL Server等支持递归CTE的主流数据库。

通用递归查询语句 #

WITH RECURSIVE geo_cte AS (
    -- 锚点：查询指定的州，同时关联获取对应的国家信息
    SELECT 
        c.Name AS Country,
        s.Name AS state,
        s.Id AS state_id,
        s.Name AS current_name,
        s.Id AS current_id,
        s.Type AS current_type
    FROM geo_hierarchy s
    JOIN geo_hierarchy c ON s.ParentId = c.Id AND c.Type = 'country'
    WHERE s.Name = 'NY' -- 此处可替换为目标州名，也可修改为s.Id = 2按ID查询
    UNION ALL
    -- 递归部分：遍历州的所有子节点
    SELECT 
        g.Country,
        g.state,
        g.state_id,
        t.Name AS current_name,
        t.Id AS current_id,
        t.Type AS current_type
    FROM geo_cte g
    JOIN geo_hierarchy t ON t.ParentId = g.current_id
)
-- 最终筛选出类型为城市的结果
SELECT 
    Country,
    state,
    current_name AS city
FROM geo_cte
WHERE current_type = 'city';

三级结构简化写法（无需递归） #

如果你的数据固定为「国家-州-城市」三级，不需要支持更深层级，可直接用多表自连接实现，兼容所有数据库版本：

SELECT 
    c.Name AS Country,
    s.Name AS state,
    city.Name AS city
FROM geo_hierarchy s
JOIN geo_hierarchy c ON s.ParentId = c.Id AND c.Type = 'country'
JOIN geo_hierarchy city ON city.ParentId = s.Id AND city.Type = 'city'
WHERE s.Name = 'NY'; -- 替换为目标州的查询条件

查询结果示例 #

运行上述查询（以查询NY州为例），返回结果如下：

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