查找SQL查询中的后代节点数 [英] Find the number of descendant nodes in a SQL query

问题描述

我目前正在创建一个组织结构图，我需要一个特定员工下所有节点的总数。



我研究过我需要使用递归SQL 。但它似乎不适用于我的查询。相反，我反复（我知道这似乎错了）将查询放在临时表中并添加员工的子项等等。有一个示例数据：

I am currently creating a Organizational Chart and I need a total count of all the nodes under a certain employee.

I researched that I need to use recursive SQL. But it seems to not be working in my query. Instead I repeatedly (I know this seems wrong) put the query in a temp table and add the Children of the employee and so on. There is a sample data:

INSERT INTO @T (ID, name, AdvisorID) VALUES
(1,  'Euler', NULL),
(2,  'Lagrange', 1),
(3,  'Laplace', 1),
(4,  'Fourier', 2),
(5,  'Poisson', 2),
(6,  'Dirichlet', 4),
(7,  'Lipschitz', 6),
(8,  'Klein', 6),
(9,  'Lindemann', 8),
(10, 'Furtwangler', 8),
(11, 'Hilbert', 9),
(12, 'Taussky-Todd', 10);

SELECT ID
, name
, AdvisorID
, (SELECT COUNT(*) FROM @T WHERE AdvisorID = e.ID) AS ChildrenCount
INTO #Temp1
FROM @T e

SELECT *
, (SELECT ISNULL((SUM(ChildrenCount)+e.ChildrenCount),0) FROM #Temp1 WHERE AdvisorID = e.ID) AS Total
INTO #Temp2
FROM #Temp1 e

SELECT * 
, (SELECT ISNULL((SUM(Total)+e.ChildrenCount),0) FROM #Temp2 WHERE AdvisorID = e.ID AS Total2
INTO #finalTemp
FROM #Temp2 e

等等on ...



感谢您的帮助。我只是想优化我的重复代码，因为在页面中加载会花费很长时间。



我尝试了什么：



如何在HierarchyID中查找父项的直接子项总数 - SQLServerCentral [ ^ ] br />
postgresql - 递归SQL - 计数层次结构中的后代 - Stack Overflow [ ^ ]

and so on...

Thanks for the help. I just want to optimize my repetitive code because it will take too long to load in the page.

What I have tried:

How to find out total number of immediate children of a parent in HierarchyID – SQLServerCentral[^]
postgresql - Recursive SQL - count number of descendants in hierarchical structure - Stack Overflow[^]

推荐答案

如果你可以使用CTE，那么基于： sql server - 计算分层SQL数据中的子项数 - Stack Overflow [ ^ ]



SQL lokks如

In case you can use CTE, then based on this: sql server - Counting number of children in hierarchical SQL data - Stack Overflow[^]

The SQL lokks like

;WITH ChildrenCTE AS (
  SELECT  RootID = ID, ID
  FROM    Tbl1
  UNION ALL
  SELECT  cte.RootID, d.ID
  FROM    ChildrenCTE cte
          INNER JOIN Tbl1 d ON d.AdvisorID = cte.ID
)
SELECT  d.ID, d.AdvisorID, d.Name, cnt.Children
FROM    Tbl1 d
        INNER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.ID

，我得到的结果是

and the result I get is

ID  AdvisorID	Name		    Children
--  ---------	-------------	--------
1	NULL	Euler              11
2	1		Lagrange           9
3	1		Laplace            0
4	2		Fourier            7
5	2		Poisson            0
6	4		Dirichlet          6
7	6		Lipschitz          0
8	6		Klein              4
9	8		Lindemann          1
10	8		Furtwangler        1
11	9		Hilbert            0
12	10		Taussky-Todd       0

这篇关于查找SQL查询中的后代节点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！