用于获取层次结构中的顶级父级的 SQL Server 函数 [英] SQL Server function to get top level parent in hierarchy

问题描述

我有下表(master_group)结构:

I have following table (master_group) structure :

code    name                      under 

1       National Sales Manager    1
2       regional sales manager    1 
3       area sales manager        2 
4       sales manager             3

如何获得特定行的最终父级，例如:

How do I get the ultimate parent of a particular row like :

code    name                      under     ultimateparent

1       National Sales Manager    1         1
2       regional sales manager    1         1
3       area sales manager        2         1
4       sales manager             3         1

推荐答案

随着递归 cte 从上到下:

With recursive cte going from top to childs:

with cte as(
  select *, code as ultimate from t where code = under
  union all
  select t.*, c.ultimate from t
  join cte c on c.code = t.under
  where t.code <> t.under
)
select * from cte

对于数据:

create table t (code int, name varchar(100), under int)
insert into t values
(1, 'National Sales Manager', 1),
(2, 'regional sales manager', 1),
(3, 'area sales manager', 2),
(4, 'sales manager', 3),
(5, 'a', 5),
(6, 'b', 5),
(7, 'c', 5),
(8, 'd', 7),
(9, 'e', 7),
(10, 'f', 9),
(11, 'g', 9)

它生成输出:

code    name                    under   ultimate
1       National Sales Manager  1       1
5       a                       5       5
6       b                       5       5
7       c                       5       5
8       d                       7       5
9       e                       7       5
10      f                       9       5
11      g                       9       5
2       regional sales manager  1       1
3       area sales manager      2       1
4       sales manager           3       1

小提琴 http://sqlfiddle.com/#!6/17c12e/1

这篇关于用于获取层次结构中的顶级父级的 SQL Server 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！