查询以计算给定节点的每个级别的子节点数 [英] Query to count no of children at every level for a given node

问题描述

我想编写一个查询，它返回层次结构树中给定节点的每个级别的子节点数。



我尝试了什么：



我在sql中有以下表结构（不要将其视为要创建的查询）表）

 表用户{
 ID  int ，
 UserId  int ，
 ParentID  int  
} 

我写了以下查询...

  DECLARE   @ T1   TABLE （
 [等级]  int ，
 MemberJoined  int  
）
 声明  @ level  int = 1，< span class =code-sdkkeyw ord> @ UserCount   int ， @ NodeFor   int  
  while   @ level < 9 
 开始 
 选择  @ UserCount  =计数（Userid）来自用户其中 ParentID   （选择用户ID 来自用户其中 ParentID < span class =code-keyword> in （ @ NodeFor ））
 
 将 插入  @ T1  值（ @ level ， @ UserCount ）
 
 选择  @NodeFor  = Userid 来自用户其中 ParentID  in （< span class =code-keyword>选择 userid 来自用户其中 ParentID 中的code-keyword>（ @ NodeFor ））
 
  set  @ level = @ level + 1 
 结束 
 选择 *从  @T1  它在第一级正确运行，但是当@NodeFor只占用第一个用户ID时所有下一个级别（假设在第一级，用户有3个孩子，那么它只需要第一个记录，但我想要全部三个）
解决方案 
你不需要使用循环... 
 
看看这个递归的公用表表达式
 
 
 ;  WITH  usr_CTE  AS （
  SELECT  UserID，ParentID，[Name] 
， 1   as  RLevel 
， MtoE = CAST（isnull（ParentID， 0 ） AS   VARCHAR （MAX））+ '  /' + 
 CAST（UserID  AS   VARCHAR （MAX））
  FROM  [用户] 
  WHERE  ParentID  IS   NULL  
  UNION   ALL  
  SELECT  e.UserID，e.ParentID，e。[Name] 
，RLevel +  1  
，MtoE = MtoE + '  /' + CAST（e.UserID  AS   VARCHAR （MAX））
  FROM  [用户] e 
  INNER   JOIN  usr_CTE ecte  ON  ecte.UserID = e.ParentID 
）
  SELECT  UserID，EC.ParentID，[Name]，RLevel，MtoE 
  FROM  usr_CTE EC 
 
 
使用我设置的虚拟数据（Northwind数据库员工的简化版本） table）我得到以下结果
 
 
 UserId父名称级别节点路径
  2  NULL Andrew Fuller  1  0/2 
  1   2  Nancy Davolio  2  0/2 / 1 
  3   2  Janet Leverling  2  0/2/3 
  4   2  Margaret Peacock  2  0/2/4 
  5   2  Steven Buchanan  2  0/2/5 
  8   2  Laura Callahan  2  0/2/8 
  6   5  Michael Suyama  3  0/2/5/6 
  7   5  Robert King  3  0/2/5/7 
  9   5  Anne Dodsworth  3  0/2/5/9 
（我在哪里搜索 ParentID IS NULL 您可以在哪里使用 ParentID（@NodeFor） 
 
因此，要获得每个级别的子项数，您只需更改最终的选择CTE：
 
 
;  WITH  usr_CTE  AS  （
  SELECT  UserID，ParentID，[Name]， 1   as  RLevel 
  FROM  [Users] 
  WHERE  ParentID  IS   NULL  
  UNION   ALL  
  SELECT  e.UserID，e.ParentID，e。[Name]，RLevel +  1  
  FROM  [用户] e 
  INNER   JOIN  usr_CTE ecte  ON  ecte.UserID = e.ParentID 
）
   SELECT  RLevel，COUNT（*）
  FROM  usr_CTE EC 
  GROUP   BY  RLevel  
 
 
给了我
 
 
等级数
 1 1 
 2 5 
 3 3 
 
 
 
 
警告：
 
根据您桌子上的数据量，您可能会收到错误
 
 < blockquote class =quote> 
 Quote：
Msg 530，Level 16，State 1，Line 1 
 
语句终止。在语句完成之前，最大递归100已经耗尽。
为了解决这个问题，您可以使用类似
 OPTION（MAXRECURSION 150）
（或许多记录）你在你的表中）该行在GROUP BY子句之后。一旦您对您的查询正常工作感到满意，那么您可以将其更改为
 OPTION（MAXRECURSION 0）
 
 
 
不能你只需使用GROUP BY：
 
 
 SELECT ParentID，Count（UserID）AS Children FROM Users 
 GROUP BY ParentID 
 
 
 
Hi,

I want to write a query which returns the no of children at every level for a given node in a hierarchy tree.



What I have tried:



I have following Table structure as in sql (don't treat this as a query to create table)

table Users{
ID int,
UserId int,
ParentID int
}






I have written following query...



DECLARE @T1 TABLE (
[Level] int,
MemberJoined int
)
declare @level int=1, @UserCount int,  @NodeFor int
while @level <9
begin
select @UserCount = Count(Userid) from Users where ParentID in (select userid from users where ParentID in (@NodeFor ))

insert into @T1 values(@level, @UserCount)

select @NodeFor =  Userid  from Users where ParentID in (select userid from users where ParentID in (@NodeFor ))

set @level=@level+1
End
select * from @T1




It runs correctly for the first level but for the all next level when @NodeFor take only first userid (suppose on first level, user have 3 children then it takes only first record but i want all three)
解决方案
You don't need to use a loop ...

Have a look at this recursive Common Table Expression

;WITH usr_CTE AS (
SELECT UserID, ParentID,[Name]
  , 1 as RLevel
  , MtoE = CAST(isnull(ParentID,0) AS VARCHAR(MAX)) + '/' + 
       CAST(UserID AS VARCHAR(MAX))
FROM [Users]
WHERE ParentID IS NULL
UNION ALL
SELECT e.UserID, e.ParentID, e.[Name]
  , RLevel + 1
  , MtoE = MtoE + '/' + CAST(e.UserID AS VARCHAR(MAX))
FROM [Users] e
INNER JOIN usr_CTE ecte ON ecte.UserID = e.ParentID
)
SELECT UserID, EC.ParentID, [Name], RLevel, MtoE
FROM usr_CTE EC


With my dummy data that I set up (a cut down version of Northwind database's Employee table) I get the following results

UserId  Parent  Name             Level  Path to Node
2	NULL	Andrew Fuller	  1	0/2
1	2	Nancy Davolio	  2	0/2/1
3	2	Janet Leverling	  2	0/2/3
4	2	Margaret Peacock  2	0/2/4
5	2	Steven Buchanan	  2	0/2/5
8	2	Laura Callahan	  2	0/2/8
6	5	Michael Suyama	  3	0/2/5/6
7	5	Robert King	  3	0/2/5/7
9	5	Anne Dodsworth	  3	0/2/5/9
(Where I have searched for ParentID IS NULL is where you would use ParentID in (@NodeFor)

So to get the number of children at each level you just change the final select from the CTE:

;WITH usr_CTE AS (
	SELECT UserID, ParentID,[Name], 1 as RLevel
	FROM [Users]
	WHERE ParentID IS NULL
UNION ALL
	SELECT e.UserID, e.ParentID, e.[Name], RLevel + 1
	FROM [Users] e
	INNER JOIN usr_CTE ecte ON ecte.UserID = e.ParentID
)
SELECT RLevel, COUNT(*)
FROM usr_CTE EC
GROUP BY RLevel


which gave me 

Level   Count
1	1
2	5
3	3




Caveat:

Depending on the volume of data in your table you may get an error

Quote:
"Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
To get around this you can use something like 
OPTION (MAXRECURSION 150)
(or however many records you have in your table) That line goes after the GROUP BY clause. Once you are happy that your query is working properly then you can change that to 
OPTION (MAXRECURSION 0)

Can't you just use a GROUP BY:

SELECT ParentID, Count(UserID) AS Children FROM Users
GROUP BY ParentID


                        
这篇关于查询以计算给定节点的每个级别的子节点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！