如何通过父标签查询关联产品？SQL实现方案咨询

现有两张数据表：

tags 标签表（树形层级结构） #

// tags
+----+-----------------------+-----------+
| id |         name          | parent_id |
+----+-----------------------+-----------+
| 1  | Home                  | Null      |
| 2  | Kitchen               | 1         |
| 3  | Serving and reception | 2         |
| 4  | Spoon                 | 3         |
| 5  | Digital               | NULL      |
| 6  | Communication         | 5         |
| 7  | Cellphone             | 6         |
+----+-----------------------+-----------+

products 产品表（关联最深层级的标签ID） #

// products
+----+------------------------------------------+--------+
| id |                name                      | tag_id | -- 关联的是最深层级的标签ID
+----+------------------------------------------+--------+
| 1  | Dinner Spoon Set,16 Pcs 7.3" Tablespoons | 4      | 
| 2  | iPhone 14 Promax                         | 7      |
+----+------------------------------------------+--------+

需要实现按标签展示产品的功能：通过标签ID 5、6、7都能查询到产品#2（iPhone 14 Promax），因为这些标签属于同一标签链路。目前仅能通过最深层标签ID（如7）查询对应产品，需写出支持上级标签ID查询关联产品的SQL语句。

最通用的方案是使用**递归CTE（Common Table Expression）**遍历标签的树形结构，获取目标标签及其所有子标签（含深层后代），再关联产品表即可得到所有关联产品。

通用SQL查询语句（支持任意层级标签） #

WITH RECURSIVE tag_hierarchy AS (
    -- 初始查询：获取目标标签本身
    SELECT id FROM tags WHERE id = 5 -- 替换为你要查询的标签ID（5/6/7均可）
    UNION ALL
    -- 递归遍历：获取所有子标签，直到无下一级
    SELECT t.id 
    FROM tags t
    JOIN tag_hierarchy th ON t.parent_id = th.id
)
SELECT p.*
FROM products p
JOIN tag_hierarchy th ON p.tag_id = th.id;

逻辑说明 #

1. 递归CTE分为两部分：
   • UNION ALL上方：选中目标标签ID，作为递归的起始节点。
   • UNION ALL下方：不断关联子标签，递归遍历整个标签分支的所有后代节点。
2. 最终将产品表与递归得到的标签ID集合关联，即可拿到所有关联该标签及其子标签的产品。
3. 替换WHERE id = 5中的数字为目标标签ID，就能得到对应结果。

兼容旧版本数据库方案（固定层级） #

如果你的数据库不支持递归CTE（如旧版MySQL），可以用多层自连接实现，但仅适合固定层级的标签结构：

SELECT p.*
FROM products p
JOIN tags t1 ON p.tag_id = t1.id
LEFT JOIN tags t2 ON t1.parent_id = t2.id
LEFT JOIN tags t3 ON t2.parent_id = t3.id
WHERE 6 IN (t1.id, t2.id, t3.id); -- 替换为目标标签ID

该方案扩展性差，标签层级变化时需修改SQL，优先推荐递归CTE方案。

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