限制递归到一定的水平 - 重复行(Limiting recursion to certain lev

要启动，这是不是我与其他同名的问题的重复，我只是想不出更好的名字为这一个！

我有一个名为Player和另一个名为单位SQL表。

每个玩家必须通过外键的UnitID属于一个团队。
各单位可通过递归场ParentUnitID属于另一个团队。
一个ParentUnit可以是ParentUnit（无限递归），但玩家只能属于一个团队。
一个单位可能有很多孩子

因此，它可能是（自上而下）...

TeamA（是顶层）
TeamB（属于^^）
TeamC（属于^^）
TeamD（属于^^）
PLAYER_1（属于^^）

我的问题是，如果我给一个玩家的PlayerID（PK的那个表），是什么让一个特定的球队中最好的方法是什么？

请参阅数据，结构和查询我SQLFiddle： http://sqlfiddle.com/#!3/78965/3

用我（从小提琴）的数据，我希望能够让每一个玩家“TeamB”之下，但随后“Player4”的顶部单元应该是‘TeamC’。为了做到这一切我想传递的是PlayerID和“TeamB”的ID。所以我说：“让所有的球员和顶级单位下TeamB然后过滤掉所有的玩家除了Player4。

编辑：我相信上面的段落应改为： 随着我的数据（从小提琴），我希望能够建立顶级球队（S），下面饰演“TeamB”。 对于每个顶级球队下面的“TeamB”我当时想建立的或低于该团队发挥所有球员。 那么我想球员名单限制在一个或多个特定球员的能力。

正如你可以看到SQLFiddle我回来多行的每个球员，我敢肯定，这是一个快速解决，但我不出来...这就是我在我的小提琴去了，但它的，以及呃，有点繁琐... :)

编辑：详细信息：

OK，所以如果想象这是一个存储过程。

我通过在PlayerIDs 1,2,3,4
我通过在单元ID 2

我希望将返回的数据看起来像

Player3, TeamC

仅返回Player3，因为它是它是TeamB（ID 2）的后代的唯一的球员，并返回TeamC因为它是最高级别的单元（下面的UnitID 2）Player3属于。

如果我不是传入：

我通过在PlayerIDs 1,2,3,4
我通过在单元ID 6

我期望

Player1, Team2
Player2, Team2

Answer 1:

这个答案已经完全重写。 原来并没有完全在所有情况下工作

我不得不改变CTE来表示每一个单位的全部单位层次作为一个可能的根（顶部单元）。它允许每单位多个孩子一个真正的层次结构。

我在扩展这个样本数据SQL小提琴有分配给两个单元11和12这正确返回正确的行为每3个玩家，在1单元的下方一定程度的机播放的球员。

“根”设备ID和玩家ID列表方便在最外面的WHERE子句的底部，因此很容易改变的ID需要。

with UnitCTE as (
  select u.UnitID,
         u.Designation UnitDesignation,
         u.ParentUnitID as ParentUnitID,
         p.Designation as ParentUnitDesignation,
         u.UnitID TopUnitID,
         u.Designation TopUnitDesignation,
         1 as TeamLevel
    from Unit u
    left outer join Unit p
      on u.ParentUnitId = p.UnitID
  union all
  select t.UnitID,
         t.Designation UnitDesignation,
         c.UnitID as ParentUnitID,
         c.UnitDesignation as ParentUnitDesignation,
         c.TopUnitID,
         c.TopUnitDesignation,
         TeamLevel+1 as TeamLevel
    from Unit t
    join UnitCTE c
      on t.ParentUnitID = c.UnitID
)
select p.PlayerID,
       p.Designation,
       t1.*
  from UnitCTE t1
  join UnitCTE t2
    on t2.TopUnitID = t1.UnitID
   and t2.TopUnitID = t1.TopUnitID
  join Player p
    on p.UnitID = t2.UnitID
 where t1.ParentUnitID = 1
   and playerID in (1,2,3,4,5,6)

这里是具有嵌入在CTE单元ID标准略微优化版本。的CTE只计算植根于单位的层次结构，其中父ID是所选择的单元ID（1在这种情况下）

with UnitCTE as (
  select u.UnitID,
         u.Designation UnitDesignation,
         u.ParentUnitID as ParentUnitID,
         p.Designation as ParentUnitDesignation,
         u.UnitID TopUnitID,
         u.Designation TopUnitDesignation,
         1 as TeamLevel
    from Unit u
    left outer join Unit p
      on u.ParentUnitId = p.UnitID
   where u.ParentUnitID = 1
  union all
  select t.UnitID,
         t.Designation UnitDesignation,
         c.UnitID as ParentUnitID,
         c.UnitDesignation as ParentUnitDesignation,
         c.TopUnitID,
         c.TopUnitDesignation,
         TeamLevel+1 as TeamLevel
    from Unit t
    join UnitCTE c
      on t.ParentUnitID = c.UnitID
)
select p.PlayerID,
       p.Designation,
       t1.*
  from UnitCTE t1
  join UnitCTE t2
    on t2.TopUnitID = t1.UnitID
  join Player p
    on p.UnitID = t2.UnitID
 where playerID in (1,2,3,4,5,6)

这是我原来的答复。 它只有在单位层次被限制到允许每单位只生一个孩子的作品。 在这个问题的SQL小提琴例子有3个孩子1号机组，因此，如果对运行1号机组它错误地返回多行的玩家3,5和6

下面是一个SQL小提琴演示该问题。

with UnitCTE as
  select UnitID,
         Designation UnitDesignation,
         ParentUnitID as ParentUnitID,
         cast(null as varchar(50)) as ParentUnitDesignation,
         UnitID TopUnitID,
         Designation TopUnitDesignation,
         1 as TeamLevel
    from Unit
   where ParentUnitID is null
  union all
  select t.UnitID,
         t.Designation UnitDesignation,
         c.UnitID,
         c.UnitDesignation,
         c.TopUnitID,
         c.TopUnitDesignation,
         TeamLevel+1 as TeamLevel
    from Unit t
    join UnitCTE c
      on t.ParentUnitID = c.UnitID
)
select p.PlayerID,
       p.Designation,
       t2.*
  from Player p
  join UnitCTE t1
    on p.UnitID = t1.UnitID
  join UnitCTE t2
    on t2.TopUnitID = t1.TopUnitID
   and t1.TeamLevel >= t2.TeamLevel
  join UnitCTE t3
    on t3.TopUnitID = t1.TopUnitID
   and t2.TeamLevel = t3.TeamLevel+1
 where t3.UnitID = 2
   and playerID in (1,2,3,4)

Answer 2:

with UnitCTE as (
  select UnitID,
         Designation,
         ParentUnitID as ParentUnitID,
         cast(null as varchar(50)) as ParentUnitDesignation,
         UnitID TopUnitID,
         Designation TopUnitDesignation,
         1 as TeamLevel
    from Unit
   where ParentUnitID is null
  union all
  select t.UnitID,
         t.Designation,
         c.UnitID,
         c.Designation,
         c.TopUnitID,
         c.TopUnitDesignation,
         TeamLevel+1 as TeamLevel
    from Unit t
    join UnitCTE c
      on t.ParentUnitID = c.UnitID
      --WHERE t.UnitID = 1
),
x AS (
select Player.PlayerID,
       pDesignation = Player.Designation, t1.*,
       rn = ROW_NUMBER() OVER (PARTITION BY Player.PlayerID ORDER BY Player.Designation)
  from Player
  join UnitCTE t1
    on Player.UnitID = t1.UnitID
  join UnitCTE t2
    on t1.TopUnitID = t2.TopUnitID
   and t2.TeamLevel=2
)
SELECT * FROM x
WHERE rn = 1
   ORDER BY TeamLevel

文章来源: Limiting recursion to certain level - Duplicate rows