如何查询表中与指定ID关联的所有父子级记录？

你需要查询任意指定ID对应的全部祖先节点、自身、全部后代节点，可通过递归CTE实现，适配绝大多数主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+）：

-- 替换your_table_name为你的实际表名
WITH RECURSIVE family_tree AS (
    -- 1. 先查目标节点自身作为递归起点
    SELECT id, name, parent 
    FROM your_table_name 
    WHERE id = @target_id -- 替换为你要查询的指定ID，比如33、21
    UNION ALL
    -- 2. 向上递归查询所有父级节点
    SELECT t.id, t.name, t.parent
    FROM your_table_name t
    INNER JOIN family_tree ft ON t.id = ft.parent
    UNION ALL
    -- 3. 向下递归查询所有子级节点
    SELECT t.id, t.name, t.parent
    FROM your_table_name t
    INNER JOIN family_tree ft ON t.parent = ft.id
)
-- 去重后返回结果
SELECT DISTINCT * FROM family_tree ORDER BY id;

逻辑说明 #

• 递归锚点先定位到你指定的ID对应的记录
• 向上递归分支每次匹配当前记录的父级ID，直到根节点（parent为null时自动停止递归）
• 向下递归分支每次匹配当前记录的子级ID，直到最末级叶子节点（没有匹配的子级时自动停止递归）
• 最后用DISTINCT去重，避免目标节点自身被重复返回

效果验证 #

当传入@target_id = 33时：

1. 锚点得到id=33的记录
2. 向上递归得到id=21、id=1的两条父级记录
3. 向下递归得到id=77的子级记录
   合并去重后返回结果和你预期的完全一致。
   当传入@target_id = 21时，返回结果和id=33完全一致，符合你的需求。

如果你使用的是不支持递归CTE的旧版本MySQL（5.7及更早），可以用自定义函数实现：

-- 1. 先定义查询所有祖先ID的函数
DELIMITER //
CREATE FUNCTION get_ancestors(target_id INT) RETURNS VARCHAR(1000)
BEGIN
    DECLARE ids VARCHAR(1000);
    DECLARE current_id INT;
    SET ids = CAST(target_id AS CHAR);
    SET current_id = target_id;
    WHILE current_id IS NOT NULL DO
        SELECT parent INTO current_id FROM your_table_name WHERE id = current_id;
        IF current_id IS NOT NULL THEN
            SET ids = CONCAT(ids, ',', CAST(current_id AS CHAR));
        END IF;
    END WHILE;
    RETURN ids;
END //
DELIMITER ;

-- 2. 再定义查询所有后代ID的函数
DELIMITER //
CREATE FUNCTION get_descendants(target_id INT) RETURNS VARCHAR(1000)
BEGIN
    DECLARE ids VARCHAR(1000);
    DECLARE temp_ids VARCHAR(1000);
    SET ids = CAST(target_id AS CHAR);
    SET temp_ids = CAST(target_id AS CHAR);
    WHILE temp_ids IS NOT NULL DO
        SELECT GROUP_CONCAT(id) INTO temp_ids FROM your_table_name WHERE FIND_IN_SET(parent, temp_ids) > 0;
        IF temp_ids IS NOT NULL THEN
            SET ids = CONCAT(ids, ',', temp_ids);
        END IF;
    END WHILE;
    RETURN ids;
END //
DELIMITER ;

-- 3. 查询时合并两个函数返回的ID即可
SELECT * FROM your_table_name 
WHERE FIND_IN_SET(id, get_ancestors(33)) OR FIND_IN_SET(id, get_descendants(33))
GROUP BY id ORDER BY id;

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