使用CTE递归SQL Server 2005获取其他节点信息

Question

背景表数据：

memberid sponsorid mailfirstname maillastname
1000     NULL      Foo           Chu
1001     1000      Foo1          Chu1
1002     1001      Foo2          Chu2
1003     1002      Foo3          Chu3
1004     1003      Foo4          Chu4

下面的脚本打印出我目前正在使用的成员。但是我也想同时打印该成员的赞助商信息。所以，目前我得到了：

Level memberid sponsorid mailfirstname maillastname
0     1000     NULL      Foo           Chu
...
2     1004     1003      Foo4          Chu4

但是对于1004会员，我也希望他们的赞助商：

Level memberid sponsorid mailfirstname maillastname sponsorfname sponsorlname
2     1004     1003      Foo4          Chu4         Foo3         Chu3

查询：

WITH Heirarchy
AS
(
   SELECT 
      CONVERT( INT , 0 ) [Level],
      T1.memberid,
      T1.sponsorid,
      T1.mailfirstname,
      T1.maillastname
   FROM
      members T1
   WHERE
      T1.memberid = 20000283
   UNION ALL
   SELECT 
      CONVERT( INT , ( TH.[Level] + 1 ) ) [Level],
      T1.memberid,
      T1.sponsorid,
      T1.mailfirstname,
      T1.maillastname
   FROM
      members T1
   INNER JOIN 
      Heirarchy TH ON TH.memberid = T1.sponsorid
)
SELECT * 
FROM Heirarchy 
ORDER BY level, maillastname

Answer 1

它应该帮助您

在下面的脚本中，我凭借其赞助商检索所有成员

WITH Heirarchy
AS
(
   SELECT 
      0 AS [Level],
      T1.memberid,
      T1.sponsorid,
      T1.mailfirstname,
      T1.maillastname,
      T1.mailfirstname AS sponsorfname,
      T1.maillastname AS sponsorlname
   FROM
      members T1
   WHERE
      T1.sponsorid IS NULL
   UNION ALL
   SELECT
      TH.[Level] + 1 AS [Level],
      T1.memberid,
      T1.sponsorid,
      T1.mailfirstname,
      T1.maillastname,
      TH.mailfirstname,
      TH.maillastname
   FROM
      members T1
   INNER JOIN 
      Heirarchy TH ON TH.memberid = T1.sponsorid
)
SELECT * 
FROM Heirarchy
ORDER BY level, maillastname

演示 sqlfiddle