如何通过标签层级链获取所有关联产品？SQL技术求助

现有两张数据表：

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 |                name                      | tag_id | -- 关联的最底层标签ID
+----+------------------------------------------+--------+
| 1  | Dinner Spoon Set,16 Pcs 7.3" Tablespoons | 4      | 
| 2  | iPhone 14 Promax                         | 7      |
| 3  | Samsung A20                              | 7      |
+----+------------------------------------------+--------+

需求：实现按标签展示产品列表的功能，传入标签ID 5、6、7中的任意一个，都需要返回以下产品数据：

| 2  | iPhone 14 Promax                         | 7      |
| 3  | Samsung A20                              | 7      |

当前使用的查询语句存在问题（仅返回单个产品，而非完整列表）：

WITH RECURSIVE cte (id, name, parent_id, orig_id) AS (
    SELECT id, name, parent_id, id AS orig_id
    FROM tags
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t1.id, t1.name, t1.parent_id, t2.orig_id
    FROM tags t1
    INNER JOIN cte t2
        ON t2.id = t1.parent_id
)

SELECT MAX(p.name) AS name
FROM cte t
LEFT JOIN products p
    ON p.tag_id = t.id
GROUP BY t.orig_id
HAVING SUM(t.id = 6) > 0;

原查询的问题在于使用了MAX(p.name)和GROUP BY t.orig_id，导致同一标签分支下的产品被合并为单条记录。正确思路是：先通过递归CTE获取目标标签及其所有子标签的ID集合，再用该集合关联产品表获取全部匹配产品。

正确查询语句（以传入标签ID=6为例） #

WITH RECURSIVE tag_hierarchy AS (
    -- 起始节点：传入的目标标签
    SELECT id, name, parent_id
    FROM tags
    WHERE id = 6  -- 替换为需要传入的标签ID
    UNION ALL
    -- 递归获取所有子标签
    SELECT t.id, t.name, t.parent_id
    FROM tags t
    JOIN tag_hierarchy th ON th.id = t.parent_id
)
-- 关联产品表，筛选匹配的产品
SELECT p.id, p.name, p.tag_id
FROM products p
WHERE p.tag_id IN (SELECT id FROM tag_hierarchy);

逻辑说明 #

1. 递归CTE部分：
   • 先选中传入的目标标签作为起始节点
   • 递归遍历该标签的所有子标签，最终得到目标标签及其所有后代标签的ID列表
2. 产品筛选部分：
   • 用子查询获取的标签ID集合，筛选产品表中tag_id属于该集合的所有产品
3. 通用性适配：
   • 只需修改WHERE id = 6中的数字，即可支持传入标签ID 5、6、7的任意场景：
     • 传入ID=5时，会获取标签5、6、7，关联得到两个手机产品
     • 传入ID=7时，仅获取标签7，同样返回两个手机产品

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