SQLServer樹形數據結構的數據進行數據統計

前言

前幾天朋友問我，關於SQLServer資料庫中對樹形結構的表數據統計問題，需求大致如下：

分類表（遞歸數據），A的子分類是B，B的子分類是C……分類關係不間斷，A為第一層，B為第二層，C為第三層……需要統計“每個分類所在的層數”、“子分類的總數”和“子分類的層數”。

解決思路：

創建示例表結構，代碼如下：

-- 分類示例表
create table temp_class
(
    classId int primary key identity(1,1),    -- 分類ID，主鍵，遞增
    className nvarchar(50),                   -- 分類名稱
    pcId int default 0,                       -- 父級分類ID，0表示最頂層
    uLevel int,                               -- 層數
    nextUCount int,                           -- 子分類的總數
    nextLevelCount int                        -- 子分類的層數
);
-- 層數欄位添加索引
-- create index ind_tempClass_uLevel on temp_class(uLevel);

-- 添加測試數據。。。。。。

步驟一：每個分類所在的層數

根據樹形數據結構的規律，在統計層數時，需要從最頂層向下累計，代碼如下：

-- 1、更新層數（pcId=0 表示第一層）
-- 更新最頂層
declare @i int=1;  -- 第一層
update temp_class set uLevel=@i where pcId=0;
while(1=1)
  begin
    if(not exists(select top 1 1 from temp_class a where exists(select top 1 1 from temp_class b where b.uLevel=@i and b.classId=a.pcId)))
      break;  -- 無下層數據，跳出迴圈
    -- 更新下一層
    update a set a.uLevel=@i+1 from temp_class a where exists(select top 1 1 from temp_class b where b.uLevel=@i and b.classId=a.pcId);
    -- 增加一層
    set @i=@i+1;
  end;

步驟二：子分類的總數

在第一步中，已經統計出了分類的層數，在統計每個分類的子分類個數時，就從最底層統計起來，本層子分類的個數就等於下一層中子分類的個數之和加上下一層的分類數量，代碼如下：

-- 2、更新子分類的總數
-- 獲取最低層分類（最大的層數）
declare @maxLevel int=1;
select @maxLevel=MAX(uLevel) from temp_class;
-- 更新最底層的子分類總數為 0
update temp_class set nextUCount=0 where uLevel=@maxLevel;
-- 從最底層向上累計子分類總數
while(1=1)
  begin
    set @maxLevel=@maxLevel-1;
    if(@maxLevel<=0)  -- 層數走完，退出
      break;
    -- 更新上一層的子分類的總數
    update a set a.nextUCount=isnull(b.nextUCount,0) from temp_class a
        left join
            -- 父級（本層）分類的個數=下一層中子分類的個數之和+下一層的分類數量
            (select pcId,SUM(nextUCount)+COUNT(classId) nextUCount from temp_class where uLevel=@maxLevel+1 group by pcId) b
            on a.classId=b.pcId
        where a.uLevel=@maxLevel;
  end;

步驟三：子分類的層數

在第一步中，已經統計出了分類的層數，在統計每個分類的子分類層數時，就從最底層統計起來，本層子分類的層數就等於下一層中子分類的層數最大值加上一層，代碼如下：

-- 3、更新子分類的層數
-- 獲取最低層子分類（最大的層數）
declare @maxLevel int=1;
select @maxLevel=MAX(uLevel) from temp_class;
-- 更新最底層的子分類層數為 0
update temp_class set nextLevelCount=0 where uLevel=@maxLevel;
-- 從最底層向上累計層數
while(1=1)
  begin
    set @maxLevel=@maxLevel-1;
    if(@maxLevel<=0)  -- 層數走完，退出
      break;
    -- 更新上一層的子分類層數
    update a set a.nextLevelCount=ISNULL(b.nextLevelCount,0) from temp_class a
        left join
            -- 父級（本層）分類的層數=下一層中子分類的最大層數+1（當前子分類為 1 層）
            (select pcId,(MAX(nextLevelCount)+1) as nextLevelCount from temp_class where uLevel=@maxLevel+1 group by pcId) b
            on a.classId=b.pcId
        where a.uLevel=@maxLevel;
  end;

查詢結果：

後言

該隨筆僅當個人筆記所用，路過的大神如有好的建議，還請賜教，菜鳥再此感激不盡！