*

标题：查询各节点的父路径函数

作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)  

时间：2008-05-12

地点：广东深圳

*/



/*

原始数据及要求结果如下：

--食品 

  --水果 

    --香蕉 

    --苹果    

  --蔬菜 

    --青菜

id          pid         name                 

----------- ----------- -------------------- 

1           0           食品

2           1           水果

3           1           蔬菜

4           2           香蕉

5           2           苹果

6           3           青菜



要求得到各节点的父路径即如下结果：

id  pid name  路径                         

--- --- ----- ---------------

1   0   食品  食品

2   1   水果  食品,水果

3   1   蔬菜  食品,蔬菜

4   2   香蕉  食品,水果,香蕉

5   2   苹果  食品,水果,苹果

6   3   青菜  食品,蔬菜,青菜 

*/



create table tb (id int , pid int , name nvarchar(20)) 

insert into tb values(1 , 0 , '食品')

insert into tb values(2 , 1 , '水果')

insert into tb values(3 , 1 , '蔬菜')

insert into tb values(4 , 2 , '香蕉')

insert into tb values(5 , 2 , '苹果')

insert into tb values(6 , 3 , '青菜')

go



--查询各节点的父路径函数

create function f_pid(@id int) returns varchar(100)

as

begin

  declare @re_str as varchar(100)

  set @re_str = ''

  select @re_str = name from tb where id = @id

  while exists (select 1 from tb where id = @id and pid <> 0)

    begin

      select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id

    end

  return @re_str

end

go



select * , dbo.f_pid(id) 路径 from tb order by id



drop table tb

drop function f_pid







SQL code

/*

标题：查询所有节点及其所有子节点的函数

作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 

时间：2009-04-12

地点：广东深圳

*/



--生成测试数据 

create table tb(id varchar(10),pid varchar(10)) 

insert into tb select 'a', null 

insert into tb select 'b', 'a' 

insert into tb select 'c', 'a' 

insert into tb select 'd', 'b' 

insert into tb select 'e', 'b' 

insert into tb select 'f', 'c' 

insert into tb select 'g', 'c' 

go 



--创建用户定义函数 

create function f_getchild(@id varchar(10)) returns varchar(8000) 

as 

begin 

  declare @i int , @ret varchar(8000) 

  declare @t table(id varchar(10) , pid varchar(10) , level int) 

  set @i = 1 

  insert into @t select id , pid , @i from tb where id = @id 

  while @@rowcount <> 0 

  begin 

    set @i = @i + 1 

    insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1

  end 

  select @ret = isnull(@ret , '') + id + ',' from @t 

  return left(@ret , len(@ret) - 1)

end 

go 



--执行查询 

select id , children = isnull(dbo.f_getchild(id) , '') from tb group by id

go 



--输出结果 

/* 

id         children     

---------- -------------

a          a,b,c,d,e,f,g

b          b,d,e

c          c,f,g

d          d

e          e

f          f

g          g



（所影响的行数为 7 行）



*/ 



--删除测试数据 

drop function f_getchild 

drop table tb







SQL code

/*

标题：查询所有顶级节点及其子节点的例

地址：http://topic.csdn.net/u/20090323/21/63a91f51-c4df-464d-ba18-64343deb4e3a.html

作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)

时间：2009-03-23

地点：广东深圳

*/



[code=SQL]create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )

insert into area values('广东省',2,0) 

insert into area values('四川省',2,0) 

insert into area values('湖北省',2,0) 

insert into area values('东莞市',1,1) 

insert into area values('广州市',1,1) 

insert into area values('天河区',0,5) 

insert into area values('绵阳市',1,2) 

insert into area values('武汉市',1,3) 

insert into area values('汉口区',0,8) 

insert into area values('随州市',1,3)

go



select * from area



drop table area



/*

id          Name       order_by    father_ID   

----------- ---------- ----------- ----------- 

1           广东省        2           0

2           四川省        2           0

3           湖北省        2           0

4           东莞市        1           1

5           广州市        1           1

6           天河区        0           5

7           绵阳市        1           2

8           武汉市        1           3

9           汉口区        0           8

10          随州市        1           3



（所影响的行数为 10 行）



要求显示为：

name           

-------------- 

广东省

  东莞市

  广州市

    天河区

四川省

  绵阳市

湖北省

  武汉市

    汉口区

  随州市



（所影响的行数为 10 行）

*/





SQL code

--创建原始表

create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )

insert into area values('广东省',2,0) 

insert into area values('四川省',2,0) 

insert into area values('湖北省',2,0) 

insert into area values('东莞市',1,1) 

insert into area values('广州市',1,1) 

insert into area values('天河区',0,5) 

insert into area values('绵阳市',1,2) 

insert into area values('武汉市',1,3) 

insert into area values('汉口区',0,8) 

insert into area values('随州市',1,3)

--创建临时表

create table tmp (id int identity,Name varchar(10) ,order_by int ,father_ID int )

go



--创建查询指定节点及其所有子节点的函数

create function f_cid(@ID int) returns @t_level table(id int , level int)

as

begin

  declare @level int

  set @level = 1

  insert into @t_level select @id , @level

  while @@ROWCOUNT > 0

  begin

    set @level = @level + 1

    insert into @t_level select a.id , @level

    from area a , @t_Level b

    where a.father_ID = b.id and b.level = @level - 1

  end

  return

end

go



--创建存储过程并将数据插入临时表

create proc my_proc 

as

begin

  declare @id as int

  set @id = 0

  while exists(select 1 from area where order_by = 2 and id > @id)

  begin

    set @id = (select min(id) from area where order_by = 2 and id > @id)

    insert into tmp(Name ,order_by ,father_ID) select a.name,a.order_by ,a.father_id from area a , f_cid(@id) b where a.id = b.id order by a.id 

  end

end

go

exec my_proc



--从临时表提取数据并显示

select case when order_by = 2 then name

            when order_by = 1 then '  ' + name

            when order_by = 0 then '    ' + name

       end name

from tmp order by id



drop function f_cid

drop proc my_proc

drop table area , tmp



/*

name           

-------------- 

广东省

  东莞市

  广州市

    天河区

四川省

  绵阳市

湖北省

  武汉市

    汉口区

  随州市



（所影响的行数为 10 行）

-- Test Data: ta

IF OBJECT_ID('[ta]') IS NOT NULL 

    DROP TABLE [ta]

Go

CREATE TABLE ta([ID] INT,[ParentID] NVARCHAR(4),[Location] NVARCHAR(21))

Go

INSERT INTO ta

   SELECT 1,Null,'I:' UNION ALL

   SELECT 2,'1','Filescenter' UNION ALL

   SELECT 3,'2','品质部' UNION ALL

   SELECT 4,'3','Archives' UNION ALL

   SELECT 5,'4','Desktop.ini' UNION ALL

   SELECT 6,'3','显示桌面.scf' UNION ALL

   SELECT 7,'3','Thumbs.db' UNION ALL

   SELECT 8,'3','Thumbs.db:encryptable' UNION ALL

   SELECT 9,'3','测 试 计 划 表.doc' UNION ALL

   SELECT 10,'3','杂文' UNION ALL

   SELECT 11,'10','Internal Audit.xls' UNION ALL

   SELECT 12,'3','生产品质' UNION ALL

   SELECT 13,'12','#1009产品每星期不良率.xls' UNION ALL

   SELECT 14,'12','633问题图片.xls' 

GO

--Start

--创建用户定义函数,每个子节点de父节点的信息

create function f_getParent1(@ID int)

returns varchar(40)

as

begin

    declare @ret varchar(4000),@bid int

   -- set @Id = 14

if  not exists(select 1 from ta where parentid = @Id)

            set @ret =  '->all'

    while exists(select 1 from ta where ID=@ID and parentID is not Null)

    begin

        select @ID=b.ID,@bid = a.parentid,@ret='\'+rtrim(b.[Location])+isnull(@ret,'')

        from

            ta a,ta b

        where

            a.ID=@ID and b.ID=a.parentID

        

      --  print @ret

    end

    

    set @ret=stuff(@ret,1,1,'')

    return @ret

end

go

--执行查询

select ID,replace(isnull(dbo.f_getParent1(ID),''),'->all','')+'\'+[Location] as parentID from ta

where right(isnull(dbo.f_getParent1(ID),''),5) = '->all'

go

drop function f_getParent1



--Result:

/*

ID          parentID

----------- -----------------------------------------

5           I:\Filescenter\品质部\Archives\Desktop.ini

6           I:\Filescenter\品质部\显示桌面.scf

7           I:\Filescenter\品质部\Thumbs.db

8           I:\Filescenter\品质部\Thumbs.db:encryptable

9           I:\Filescenter\品质部\测 试 计 划 表.doc

11          I:\Filescenter\品质部\杂文\Internal Audit.xls

13          I:\Filescenter\品质部\生产品质\#1009产品每星期不良率.xls

14          I:\Filescenter\品质部\生产品质\633问题图片.xls



(8 行受影响)



*/

--End 

CREATE TABLE tab([ID] INT,[ParentID] INT,[Location] NVARCHAR(21))

Go

INSERT INTO tab

   SELECT 1,Null,N'I:' UNION ALL

   SELECT 2,1,N'Filescenter' UNION ALL

   SELECT 3,2,N'品质部' UNION ALL

   SELECT 4,3,N'Archives' UNION ALL

   SELECT 5,4,N'Desktop.ini' UNION ALL

   SELECT 6,3,N'显示桌面.scf' UNION ALL

   SELECT 7,3,N'Thumbs.db' UNION ALL

   SELECT 8,3,N'Thumbs.db:encryptable' UNION ALL

   SELECT 9,3,N'测 试 计 划 表.doc' UNION ALL

   SELECT 10,3,N'杂文' UNION ALL

   SELECT 11,10,N'Internal Audit.xls' UNION ALL

   SELECT 12,3,N'生产品质' UNION ALL

   SELECT 13,12,N'#1009产品每星期不良率.xls' UNION ALL

   SELECT 14,12,N'633问题图片.xls' 

GO

--drop table tab



create function ufn_bom(@id int)

returns nvarchar(4000) as

begin

declare @loc nvarchar(4000)



;with cte as(

select ParentID,Location from tab where ID=@id

union all

select t1.ParentID,t1.Location from tab t1 join cte t2 on t2.ParentID=t1.ID

)

select @loc=stuff((select N'\'+t2.Location from tab t1 

join cte t2 on t2.ParentID=t1.ID for xml path(N'')),1,1,

(select Location+N'\' from tab where ParentID is null))



return @loc

end



select dbo.ufn_bom(ID) from tab where ID NOT IN

(select distinct ParentID from tab where ParentID is not null)

/*

I:\Filescenter\品质部\Archives\Desktop.ini

I:\Filescenter\品质部\显示桌面.scf

I:\Filescenter\品质部\Thumbs.db

I:\Filescenter\品质部\Thumbs.db:encryptable

I:\Filescenter\品质部\测 试 计 划 表.doc

I:\Filescenter\品质部\杂文\Internal Audit.xls

I:\Filescenter\品质部\生产品质\#1009产品每星期不良率.xls

I:\Filescenter\品质部\生产品质\633问题图片.xls

*/