树表分级统计

针对树表的分级统计，Oracle中可以用CONNECT_BY_ROOT轻松实现，而SQL Server则没有简单类似的语法，借鉴其思路可使用以下CTE查询实现类似CONNECT_BY_ROOT的功能：

-- 0.准备测试数据
CREATE TABLE #data (id int,ParentID int,value int);

INSERT INTO #data
      SELECT 1 , 0, 10
UNION SELECT 2 , 0, 20
UNION SELECT 3 , 1, 30
UNION SELECT 4 , 3, 40
UNION SELECT 5 , 4, 50
UNION SELECT 6 , 2, 60
UNION SELECT 7 , 3, 70
UNION SELECT 8 , 7, 80
UNION SELECT 9 , 8, 90
;

DECLARE @RootID int;  -- 这里使用变量,实际使用时一般为参数

DECLARE @RootLevel int;
SELECT @RootID = 3;

-- 计算传入结点的Level(根结点为1)
;WITH _P AS (
SELECT ParentID,id FROM #data WHERE id = @RootID
UNION ALL
SELECT a.ParentID,a.id
  FROM #data a INNER JOIN _P cte ON a.id = cte.ParentID
)
SELECT @RootLevel = count(*) FROM _P;

;WITH
-- 1.取到所有下级
_tree AS (
SELECT ParentID,id,@RootLevel Level, CASE WHEN EXISTS(SELECT 1 FROM #data t1 WHERE t1.ParentID = t.id) THEN 0 ELSE 1 END isLeaf/*是否为叶子结点(无下级)*/
  FROM #data t WHERE id = @RootID
UNION ALL
SELECT a.ParentID,a.id, cte.Level + 1, CASE WHEN EXISTS(SELECT 1 FROM #data t1 WHERE t1.ParentID = a.id) THEN 0 ELSE 1 END
  FROM #data a INNER JOIN _tree cte ON cte.id = a.ParentID
),
-- 2.再展开每一行的所有下级
_t AS(
SELECT a.id PertainID/*所属上级*/,a.id, a.Level, a.isLeaf FROM _tree a
UNION ALL
SELECT cte.PertainID,a.id, a.Level, a.isLeaf
  FROM _tree a INNER JOIN _t cte ON cte.id = a.ParentID
)
--_t这个公用表即为类似CONNECT_BY_ROOT的结果集，我们可以利用_t这个公用表结合GROUP BY方便的统计出每级的聚合结果。
--简单查看
--SELECT * FROM _T ORDER BY 1,3;

 

-- 3.分级统计
SELECT PertainID, sum(t.value) 合计
  FROM _t INNER JOIN #data t ON _t.id = t.id
-- WHERE _t.isLeaf = 0 -- 统计到非叶子结点(具有下级才算统计结果)
 GROUP BY PertainID;

DROP TABLE #data;