Mysql
【PID是表，id主键，pid父id】

#查询子节点(向下)含自己,id=3
SELECT T2.level_, T3.* 
  FROM( 
    SELECT @codes as _ids, 
            ( SELECT @codes := GROUP_CONCAT(id) 
                FROM PID 
               WHERE FIND_IN_SET(pid, @codes) 
            ) as T1, 
           @l := @l+1 as level_ 
      FROM PID, 
          (SELECT @codes :='3', @l := 0 ) T4
     WHERE @codes IS NOT NULL 
       ) T2, PID T3 
  WHERE FIND_IN_SET(T3.id, T2._ids) 
  ORDER BY level_, id
    ;

  #查询父节点含自己,传参id=6
  SELECT T2.level_, T3.* 
  FROM( 
    SELECT @code as _code, 
            ( SELECT @code := pid 
                FROM PID 
               WHERE id = @code 
            ) as T1, 
           @l := @l+1 as level_ 
      FROM PID, 
          (SELECT @code := '6', @l := 0 ) T4 
     WHERE @code is not null
    ) T2, PID T3
 WHERE T2._code = T3.id 
 ORDER BY level_
 ;

Mysql8.0之后
(向下)
WITH RECURSIVE recursive_query AS (
  SELECT id, pid, name, level
  FROM PID
  WHERE id = 6
  UNION ALL
  SELECT p.id, p.pid, p.name, p.level
  FROM PID p
  INNER JOIN recursive_query r ON p.pid = r.id
)
SELECT *
FROM recursive_query;

-- 查询父节点  含自己（向上）
 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
      from sys_region T1
     where T1.code='370171401000'
  UNION ALL
    
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
     WHERE T2.code=T3.parent_code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
    ;
————————————————
版权声明：本文为CSDN博主「十&年」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xubenxismile/article/details/107662209

Oracle

（向下查找）
SELECT
    *
FROM
    TABLE_A a
WHERE
    条件1
START WITH  a.A_ID='111'
CONNECT BY PRIOR a.A_ID=a.A_PARENT_ID

（向上查找）
SELECT
    *
FROM
    TABLE_A a
WHERE
    条件1
START WITH  a.A_ID='111'
CONNECT BY a.A_ID=PRIOR  a.A_PARENT_ID