如果你可以使用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