sql递归函数-寻找管理者

发布于 2024-09-25 22:36:38 字数 418 浏览 0 评论 0原文

假设我有下表

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

基本上要求是将所有经理拉到您正在搜索的 user_id 下。因此，例如，如果我发送“Linda”，那么它应该返回我：

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

或者如果我发送“Mark”，那么它应该返回我：

Steve

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

原文

Lets say I have the following table

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

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'

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

-残月青衣踏尘吟 2024-10-02 22:36:38

使用：

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')

Use:

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

Resultset:

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

Scripts:

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')

回复收藏 0 原文