如何编写SQL查询获取指定ID及其子节点的层级数据

针对你的层级结构需求，最适合用**递归公共表表达式（CTE）**来实现，它能自动遍历所有层级的子节点，同时包含你传入的根节点，比简单的or条件更符合层级遍历的需求。

假设你的表名为your_table，包含id、parent_id及其他业务字段，SQL语句如下：

WITH RECURSIVE hierarchy AS (
    -- 第一步：获取传入的根节点记录
    SELECT id, parent_id, [其他业务字段]
    FROM your_table
    WHERE id = ?  -- 此处传入目标id值
    UNION ALL
    -- 第二步：递归拉取所有子节点
    SELECT t.id, t.parent_id, t.[其他业务字段]
    FROM your_table t
    INNER JOIN hierarchy h ON t.parent_id = h.id
)
SELECT * FROM hierarchy;

代码说明 #

• 基础查询块：先定位到你传入id对应的根节点记录。
• 递归关联块：通过parent_id与上一层级的id关联，循环拉取所有子节点，直到没有更多子节点为止。
• 最终查询返回整个层级结构的所有记录，包括根节点和所有子孙节点。

示例效果 #

当传入id=1时，查询会返回：

• id=1的根节点记录
• parent_id=1的id=2记录
• 如果id=2还有子节点（比如id=3的parent_id=2），也会自动包含进来，完全覆盖整个层级。

注意事项 #

• 替换your_table为你的实际表名，[其他业务字段]替换为需要查询的具体字段。
• 递归CTE需要数据库版本支持：MySQL 8.0+、PostgreSQL 9.4+、SQL Server 2008+等主流数据库均支持该语法。

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