sql - 我如何从 self 引用表中找到 parent 和 child 。？

Question

这是我的表的 (emp_details) 内容。 emp_id 是“主键”，emp_man_id 是引用 emp_detais(emp_id) 的“外键”

self 引用表:

emp_id    emp_name  emp_place  emp_man_name    emp_man_id
    1     arun      klm        arun  
    2     nivin     thr        arun             1
    3     vinay     ekm        arun             1
    4     ajo       plk        nivin            2
    5     alan      knr        nivin            2
    6     ravi      tvm        vinay            3
    7     vipin     mlp        vinay            3
    8     ani       ksd        ajo              4
    9     vino      pta        ajo              4
    11    sarat     wyd        alan             5
    10    siva      alp        alan             5

如果我将值 10 作为员工 ID 传递，我需要为此表编写一个函数；

我需要这样的输出

emp_id emp_man_id
  10         5
  5          2
  2          1

这是我的功能:

CREATE OR REPLACE FUNCTION emp_e1(IN id integer) 
  RETURNS TABLE(em_id integer, emp_mid integer) 
AS 
$BODY$ 
begin 

  return query 
    with recursive d as ( 
       select emp_id, emp_man_id, 0 as level 
       from emp_details 
       where emp_id = id 
       UNION ALL 
       select c.emp_id, c.emp_man_id, level+1 
       from d 
         inner join emp_details c on c.emp_man_id = d.emp_id 
    ) 
    SELECT * 
    FROM d; 
  end; 
$BODY$ 
LANGUAGE plpgsql;

Answer 1

(编辑: 答案是在编辑问题并向其添加 postgresql 标记之前，在第一个问题的评论中 SQL Server已被提及(我不确定也许我没有注意到，我错了)，这就是为什么首先为 MS SQL Server 提供答案但是 postgresql 的形式在编辑问题并向其添加 postgresql 标记后，也会立即添加查询。)

---

(对于 MS SQL Server)

使用递归cte:

CREATE FUNCTION dbo.fn(@empId int)
RETURNS @t table (empid int, manid int)
as
begin

with cte (empid, manid) as
(
  select emp_id,man_id
  from emp where emp_id=@empId
  union all
  select e.emp_id, e.man_id
  from emp e 
  join cte on e.emp_id=cte.manid
)
insert into @t
select * from cte;
return
end

一个SQLFIDDLE DEMO

---

(对于 Postgresql)

使用PostgreSQL 只需在cte 之前添加recursive 关键字，并将函数格式更改为Posgres 格式。

with recursive cte (empid, manid) as
(
  select emp_id,man_id
  from emp where emp_id=10
  union all
  select e.emp_id, e.man_id
  from emp e 
  join cte on e.emp_id=cte.manid
)
select * from cte

SQLFIDDLE DEMO