SQL Server内部连接到该查询再次内连接相同

We are working with MLM project. The basic idea of this project is like

我们正在与MLM项目合作。这个项目的基本思路就像

A has been introduced by
A已被介绍
B has been introduced by A
B已经由A引入
C has been introduced by B
C已经引入了B.
D has been introduced by C
D已由C引入

My SQL Server table structure is like

我的SQL Server表结构就像

user_details

user_details

https://i.stack.imgur.com/xcHGd.jpg

Now theoretically

现在理论上

A's Team Members are => B , C , D  
    B's Team Members are => C , D 
    C's Team Member is => D
    D's Team Member is => null (he doesn't have any team members yet)

Using a SQL query, how can I find team members of 'A'?

使用SQL查询，我如何找到'A'的团队成员？

1 个解决方案

#1

You can use a recursive SELECT statement as follows:-

您可以使用递归SELECT语句，如下所示： -

DECLARE @LeadName CHAR(1) = 'A';

WITH Team AS (
     SELECT ID, F_NAME, L_NAME, INTRODUCED_BY FROM mlm 
     WHERE F_NAME = @LeadName
     UNION ALL
     SELECT M.ID, M.F_NAME, M.L_NAME, M.INTRODUCED_BY FROM mlm M 
         JOIN Team T ON M.INTRODUCED_BY = T.ID
)
SELECT * FROM Team WHERE F_NAME <> @LeadName;

Here the variable @LeadName is the F_NAME of the desired root ('A' in your case).

这里变量@LeadName是所需根的F_NAME（在您的情况下为'A'）。

#1