sql: Expressing a Child-Parent-Grandparent Relationship Problem

msyql 8.0

with recursive x (tree,mgr,depth)
 as (
 select cast(ename as CHAR(100)) as ename,
 mgr, 0
 from emp
 where ename = 'MILLER'
 union all
 select cast(concat(x.tree,'-->',e.ename) as char(100)),
 e.mgr, x.depth+1
 from emp e, x
 where x.mgr = e.empno
 )
 select tree leaf___branch___root
 from x
 where depth = 2;

　　

 

postgretsql 16

-- Expressing a Child-Parent-Grandparent Relationship Problem
WITH RECURSIVE x (tree, mgr, depth) AS (
    -- 锚点查询：第一列别名改为 tree，与 CTE 定义匹配
    SELECT 
        CAST(ename AS VARCHAR(100)) AS tree,  -- 修正1：别名改为 tree，且用 VARCHAR 更灵活（PostgreSQL 推荐）
        mgr, 
        0 AS depth
    FROM emp
    WHERE ename = 'MILLER'  -- 起始节点：MILLER
    
    UNION ALL
    
    -- 递归查询：正确引用 x.tree，关联条件保持逻辑正确
    SELECT 
        CAST(CONCAT(x.tree, '-->', e.ename) AS VARCHAR(100)) AS tree,  -- 修正2：x.tree  now 有效
        e.mgr, 
        x.depth + 1 AS depth
    FROM emp e
    INNER JOIN x ON e.empno = x.mgr  -- 关联逻辑：上级的 empno = 当前节点的 mgr（向上递归找领导）
)
SELECT tree AS leaf___branch___root
FROM x
WHERE depth = 2;  -- 查询深度为 2 的路径（MILLER -> 直接领导 -> 间接领导）

　　

 

sql server 2019

-- Expressing a Child-Parent-Grandparent Relationship Problem

with x (tree,mgr,depth)
as (
 select cast(ename as varchar(100)),
 mgr, 0
 from emp
 where ename = 'MILLER'
 union all
 select cast(x.tree+'-->'+e.ename as varchar(100)),
 e.mgr, x.depth+1
 from emp e, x
 where x.mgr = e.empno
 )
 select tree leaf___branch___root
 from x
 where depth = 2;

　　

 

oracle 21c

-- Expressing a Child-Parent-Grandparent Relationship Problem
select ltrim(
 sys_connect_by_path(ename,'-->'),
 '-->') leaf___branch___root
 from emp
 where level = 3
 start with ename = 'MILLER'
 connect by prior mgr = empno