如何优化多级位置关联标签查询？实现单SQL获取全层级标签

我有一个locations表，结构如下：

另有一个tags表，与locations表为belongs_to关联关系，结构如下：

位置的所有标签通过以下URL模式的网页展示：

• http://localhost:4000/Germany
• http://localhost:4000/Germany/Hessen
• http://localhost:4000/Germany/Hessen/Frankfurt
• http://localhost:4000/Germany/Hessen/Frankfurt/Example
• http://localhost:4000/:country_slug/:federal_state_slug/:city_slug/:street_slug

当前我获取最后一个URL对应的所有标签（包含国家、联邦州、城市、街道层级的所有标签）的方式是：逐级查询每个slug对应的location，结合parent_location_id验证层级关系，收集所有location id后查询tags表。这种方式在最坏情况下（街道层级）需要执行5次SQL查询，效率极低。

我想知道是否有更优的数据结构（可重构数据库）或SQL技巧，能够通过一次SQL查询完成该需求？

解决方案 #

方案一：递归CTE（无需修改现有数据库结构） #

利用SQL的递归公共表表达式（CTE），可以一次性遍历从目标节点到顶层节点的所有层级，再关联tags表获取所有标签。

以获取Germany/Hessen/Frankfurt/Example对应的所有标签为例，SQL语句如下：

WITH RECURSIVE location_hierarchy AS (
    -- 起始节点：匹配最底层的街道slug
    SELECT id, slug, parent_location_id
    FROM locations
    WHERE slug = 'Example' AND type = 'Street'
    UNION ALL
    -- 递归向上查询父节点
    SELECT l.id, l.slug, l.parent_location_id
    FROM locations l
    JOIN location_hierarchy h ON l.id = h.parent_location_id
)
SELECT t.名称
FROM tags t
JOIN location_hierarchy h ON t.location_id = h.id;

如果要适配动态URL参数，避免不同层级slug重复导致的错误，可以增加层级验证：

WITH RECURSIVE location_hierarchy AS (
    SELECT id, slug, parent_location_id, type, 1 AS level
    FROM locations
    WHERE slug = 'Example' AND type = 'Street'
    UNION ALL
    SELECT l.id, l.slug, l.parent_location_id, l.type, h.level + 1
    FROM locations l
    JOIN location_hierarchy h ON l.id = h.parent_location_id
)
SELECT t.名称
FROM tags t
JOIN location_hierarchy h ON t.location_id = h.id
-- 验证完整路径的正确性
WHERE EXISTS (SELECT 1 FROM location_hierarchy WHERE level=4 AND slug='Germany' AND type='Country')
  AND EXISTS (SELECT 1 FROM location_hierarchy WHERE level=3 AND slug='Hessen' AND type='FederalState')
  AND EXISTS (SELECT 1 FROM location_hierarchy WHERE level=2 AND slug='Frankfurt' AND type='City')
  AND EXISTS (SELECT 1 FROM location_hierarchy WHERE level=1 AND slug='Example' AND type='Street');

方案二：重构数据结构，提升查询性能 #

如果需要更高的查询效率，可以选择以下两种层级数据存储方案：

1. 路径枚举（Path Enumeration）

在locations表新增path字段，存储从根节点到当前节点的完整slug路径：

查询时直接通过路径匹配所有层级节点：

WITH target_location AS (
    SELECT path
    FROM locations
    WHERE path = 'Germany/Hessen/Frankfurt/Example'
)
SELECT t.名称
FROM tags t
JOIN locations l ON t.location_id = l.id
JOIN target_location tl ON tl.path LIKE l.path || '/%' OR l.path = tl.path;

2. 闭包表（Closure Table）

新增location_closure表，存储所有节点的祖先-后代关系（包含节点自身）：

查询时只需找到目标节点ID，通过闭包表关联所有祖先节点：

SELECT t.名称
FROM tags t
JOIN location_closure lc ON t.location_id = lc.ancestor_id
WHERE lc.descendant_id = (SELECT id FROM locations WHERE slug = 'Example' AND type = 'Street');

闭包表的优势是查询速度极快，但需要在节点新增、删除时维护闭包表的数据。

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