如何从自我参考表中找到父母和子女。 [英] How I can find parent and child from self reference table.?

问题描述

这是我表格的（emp_details）内容。 emp_id是主键，emp_man_id是外键，其中引用了emp_detais（emp_id）

Here is my table's (emp_details) content. emp_id is "primary key" and emp_man_id is "foreign key" with references to emp_detais(emp_id)

自引用表：

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传递；

I need to write a function for this table, if I pass the value 10 as employee 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;

推荐答案

（ EDIT： answer是在编辑问题并向其添加 postgresql 标记之前，在第一个 SQL Server 的问题注释中提到的（不确定我可能没有注意到，而我错了），这就是为什么首先为 MS SQL Server 提供答案的原因，而为 postgresql提供答案形式的查询也将在问题编辑并向其添加postgresql标记后立即添加。）

( answer was before editing the question and adding postgresql tag to it, in comments under question at the first the SQL Server has been mentioned(am not sure maybe I didn't notice well and I was wrong), that's why the answer first provided for MS SQL Server however the postgresql form of the query is also added immediately after edit of question and adding postgresql tag to it.)

（用于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

a SQLFIDE DEMO

（用于Postgresql）

与 PostgreSQL 仅添加递归 cte 之前的关键字，并将函数格式更改为Posgres格式。

with PostgreSQL just add the recursive keyword before cte and change the function format to Posgres format.

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

这篇关于如何从自我参考表中找到父母和子女。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！