sql递归函数——查找管理器 [英] sql recursive function - to find managers

问题描述

假设我有下表

User_ID  Manager_ID  
---------------------
Linda        Jacob  
Mark         Linda  
Kevin        Linda  
Steve        Mark  
John         Kevin

基本上要求是拉取您正在搜索的 user_id 下的所有经理.因此，例如，如果我发送Linda"，那么它应该返回给我:

Basically the requirement is to pull all the managers under the user_id you are searching for. So for instance if I send in 'Linda' then it should return me:

'Mark', 'Kevin', 'Steve', 'John'  

或者如果我发送Mark"，那么它应该返回给我:

or if I send in 'Mark' then it should return me:

Steve

我听说过递归函数，但我不确定如何做到这一点.任何帮助，将不胜感激.

I have heard of recursive function but I am unsure of how to do this. Any help would be appreciated.

推荐答案

使用:

WITH hieararchy AS (
   SELECT t.user_id
     FROM YOUR_TABLE t
    WHERE t.manager_id = 'Linda'
   UNION ALL
   SELECT t.user_id
     FROM YOUR_TABLE t
     JOIN hierarchy h ON h.user_id = t.manager_id)
SELECT x.*
  FROM hierarchy x

结果集:

user_id
--------
Mark
Kevin
John
Steve

脚本:

CREATE TABLE [dbo].[YOUR_TABLE](
 [user_id] [varchar](50) NOT NULL,
 [manager_id] [varchar](50) NOT NULL
)

INSERT INTO YOUR_TABLE VALUES ('Linda','Jacob')
INSERT INTO YOUR_TABLE VALUES ('Mark','Linda')
INSERT INTO YOUR_TABLE VALUES ('Kevin','Linda')
INSERT INTO YOUR_TABLE VALUES ('Steve','Mark')
INSERT INTO YOUR_TABLE VALUES ('John','Kevin')

这篇关于sql递归函数——查找管理器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！