SQL Server 中樹形表資料的處理總結

-- 使用函數的方法：

--建立 示範環境

if object_id('tb_bookInfo') is not null drop table tb_bookInfo
go
create table tb_bookInfo(number int,name varchar(10),type int)
insert tb_bookInfo
select 1 ,'n1', 6 union all
select 2 ,'n2', 3

if object_id('tb_bookType') is not null drop table tb_bookType
go
create table tb_bookType(id int,typeName varchar(10),parentid int)
insert tb_bookType
select 1,'英語',0 union all
select 2,'生物',0 union all
select 3,'電腦',0 union all
select 4,'口語',1 union all
select 5,'聽力',1 union all
select 6,'資料庫',3 union all
select 7,'軟體工程',3 union all
select 8,'SQL Server',6

select a.*,b.level from tb_bookInfo  a,f_getC(3) b  where a.type=b.id  order by b.level 
/*
number      name       type        level      
----------- ---------- ----------- -----------
2           n2         3           0
1           n1         6           1

（所影響的行數為 2 行）
*/
--查所有父結點
if object_id('f_getP') is not null drop function f_getP
go
create function f_getP(@id int) 
returns @re table(id int,level int) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0 
    begin 
 set @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level 
    return 
end 
go 

--查所有子結點
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int) 
returns @re table(id int,level int) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return 
end 
go 

--查所有父子結點
if object_id('f_getAll') is not null drop function f_getAll
go
create function f_getAll(@id int) 
returns @re table(id int,level int) 
as 
begin 
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0 
    begin 
 set @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level 
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return
end 
go  
 

--刪除示範

drop table tb_bookInfo

drop table tb_bookType

drop function f_getP

drop function f_getC
drop function f_getAll
GO

 

 

 

--sqlserver2005的新方法

-- 建立示範環境
IF OBJECT_ID('[Dept]') IS NOT NULL
    DROP TABLE [Dept]
GO
CREATE TABLE Dept(
 id int PRIMARY KEY,
 parent_id int,
 name nvarchar(20))
INSERT Dept
SELECT 1, 0, N'財務部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'業務部' UNION ALL
SELECT 4, 0, N'業務部' UNION ALL
SELECT 5, 4, N'銷售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'軟體開發' UNION ALL
SELECT 9, 8, N'內部開發'
GO
--1、父-〉子
-- 查詢指定部門下面的所有部門
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
 -- 錨點成員
 SELECT * FROM Dept WHERE name = @Dept_name
 UNION ALL
 -- 遞迴成員, 通過引用CTE自身與Dept基表JOIN實現遞迴
 SELECT A.*  FROM Dept A, DEPTS B  WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO
--結果如下
/*
id          parent_id   name                
----------- ----------- --------------------
6           4           MIS
7           6           UI
8           6           軟體開發
9           8           內部開發

（所影響的行數為 4 行）
*/

--2、子-〉父
-- 查詢指定部門下面的所有部門
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'內部開發'
;WITH
DEPTS AS(
 -- 錨點成員
  SELECT * FROM Dept WHERE name = @Dept_name
--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent  FROM Dept where @Dept_name
 UNION ALL
 -- 遞迴成員, 通過引用CTE自身與Dept基表JOIN實現遞迴
 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id
)
SELECT * FROM DEPTS
GO

--結果如下
/*
id          parent_id   name                
----------- ----------- --------------------
9           8           內部開發
8           6           軟體開發
6           4           MIS
4           0           業務部

（所影響的行數為 4 行）
*/

-- 刪除示範環境
DROP TABLE Dept

 

 

 

一個不錯的例子

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([modeid] int,modename varchar(20),parentid int)
insert [tb]
select 100 ,'商品管理', 0 union all
select 101 ,'定單管理', 0 union all
select 102 ,'使用者管理', 0 union all
select 104 ,'學院廣告', 0 union all
select 105 ,'系統設定', 0 union all
select 106 ,'附件管理', 0 union all
select 107 ,'商品管理', 100 union all
select 108 ,'明細管理', 100 union all
select 109 ,'物流管理', 100 union all
select 110 ,'商品資訊管理', 107 union all
select 111 ,'商品分類管理', 107 union all
select 112 ,'資源回收筒管理', 107 union all
select 114 ,'團購管理', 108 union all
select 115 ,'拍賣管理', 108 union all
select 116 ,'優惠管理', 108 union all
select 117 ,'會員管理', 102 union all
select 118 ,'會員卡管理', 102 union all
select 119 ,'資金管理', 102 union all
select 120 ,'管理員管理', 102 union all
select 121 ,'添加管理員', 120 union all
select 122 ,'修改管理員', 120
go

--查所有子結點
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int) 
returns @re table(id int,level int,sort varchar(10)) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l,null
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.modeid,@l,ltrim(isnull(b.sort,a.modeid)) from tb as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    update @re set level = level -1
    return 
end 
go 

select a.modeid,a.parentid,REPLICATE('  ',b.level) +'┝'+a.modename,b.level,b.sort from tb  a,f_getC(0) b 
where a.modeid=b.id 
order by case when b.level<2 then 0 else 1 end,b.sort,b.level

/*
modeid      parentid                                                       sort       level      
----------- ----------- -------------------------------------------------- ---------- -----------
100         0           ┝商品管理                                              100        0
107         100           ┝商品管理                                            100        1
108         100           ┝明細管理                                            100        1
109         100           ┝物流管理                                            100        1
101         0           ┝定單管理                                              101        0
102         0           ┝使用者管理                                              102        0
117         102           ┝會員管理                                            102        1
118         102           ┝會員卡管理                                           102        1
119         102           ┝資金管理                                            102        1
120         102           ┝管理員管理                                           102        1
104         0           ┝學院廣告                                              104        0
105         0           ┝系統設定                                              105        0
106         0           ┝附件管理                                              106        0
110         107             ┝商品資訊管理                                        100        2
111         107             ┝商品分類管理                                        100        2
112         107             ┝資源回收筒管理                                         100        2
114         108             ┝團購管理                                          100        2
115         108             ┝拍賣管理                                          100        2
116         108             ┝優惠管理                                          100        2
121         120             ┝添加管理員                                         102        2
122         120             ┝修改管理員                                         102        2

（所影響的行數為 21 行）

*/

 

--查所有子結點,帶路徑與排序
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int) 
returns @re table(id int,level int,sort varchar(100),path varchar(500)) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re
 select [modeid],@l,right('00000'+ltrim(modeid),5),modename
 from tb where parentid=@id
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re
  select a.modeid,@l,b.sort+right('00000'+ltrim(a.modeid),5),
      b.path+' - '+a.modename
  from tb as a,@re as b 
  where b.id=a.parentid and b.level=@l-1
    end
    update @re set level = level
    return 
end 
go 

select a.modeid,a.parentid,REPLICATE('  ',b.level) +'┝'+a.modename,b.level,b.sort ,b.path from tb  a,f_getC(0) b 
where a.modeid=b.id 
order by sort

/*
modeid      parentid                         level                           
----------- ----------- -------------------- ----------- -------------------- ----------------------------------------
100         0           ┝商品管理                0           00100                商品管理
107         100           ┝商品管理              1           0010000107           商品管理 - 商品管理
110         107             ┝商品資訊管理          2           001000010700110      商品管理 - 商品管理 - 商品資訊管理
111         107             ┝商品分類管理          2           001000010700111      商品管理 - 商品管理 - 商品分類管理
112         107             ┝資源回收筒管理           2           001000010700112      商品管理 - 商品管理 - 資源回收筒管理
108         100           ┝明細管理              1           0010000108           商品管理 - 明細管理
114         108             ┝團購管理            2           001000010800114      商品管理 - 明細管理 - 團購管理
115         108             ┝拍賣管理            2           001000010800115      商品管理 - 明細管理 - 拍賣管理
116         108             ┝優惠管理            2           001000010800116      商品管理 - 明細管理 - 優惠管理
109         100           ┝物流管理              1           0010000109           商品管理 - 物流管理
101         0           ┝定單管理                0           00101                定單管理
102         0           ┝使用者管理                0           00102                使用者管理
117         102           ┝會員管理              1           0010200117           使用者管理 - 會員管理
118         102           ┝會員卡管理             1           0010200118           使用者管理 - 會員卡管理
119         102           ┝資金管理              1           0010200119           使用者管理 - 資金管理
120         102           ┝管理員管理             1           0010200120           使用者管理 - 管理員管理
121         120             ┝添加管理員           2           001020012000121      使用者管理 - 管理員管理 - 添加管理員
122         120             ┝修改管理員           2           001020012000122      使用者管理 - 管理員管理 - 修改管理員
104         0           ┝學院廣告                0           00104                學院廣告
105         0           ┝系統設定                0           00105                系統設定
106         0           ┝附件管理                0           00106                附件管理

(21 行受影響)

*/

 

 

----------

--刪除節點

--建立測試環境
IF OBJECT_ID('GoodType') IS NOT NULL  DROP TABLE GoodType
GO
CREATE TABLE GoodType
(
 id int ,
 name varchar(20),  
 pid int,
 tree int
)
GO

INSERT GoodType
select '1','a','0','0'
union all select '2','aa','1','1'
union all select '3','ab','1','1'
union all select '4','aaa','2','2'
union all select '5','aba','3','2'
union all select '6','abaa','5','3'
union all select '7','abab','5','3'
go
create trigger trd_GoodType on GoodType instead of delete
as
begin
 update t set t.pid=d.pid ,t.tree=t.tree-1
 from GoodType t,deleted d
 where t.pid=d.id
 delete GoodType where id in( select id from deleted)
end
go
create trigger tru_GoodType on GoodType for update
as
begin
 update t set t.tree=t.tree-1
 from GoodType t,inserted i,deleted d
 where t.pid=i.id and t.pid=d.id and i.tree=d.tree-1
end
go
--查詢
delete GoodType where id=3
select * from GoodType
go
--結果
/*

(7 行受影響)

(2 行受影響)

(1 行受影響)

(1 行受影響)

(1 行受影響)
id          name                 pid         tree
----------- -------------------- ----------- -----------
1           a                    0           0
2           aa                   1           1
4           aaa                  2           2
5           aba                  1           1
6           abaa                 5           2
7           abab                 5           2

(6 行受影響)
*/