查询树形节点各结点的路径

*

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

 

*/

 

/*

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

--食品 

  --水果 

    --香蕉 

    --苹果    

  --蔬菜 

    --青菜

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

 

 

/*

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

 

*/

 

--生成测试数据 

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 SERVER 2000中查询指定节点及其所有子节点的函数(字符串形式显示)

 

*/

 

--生成测试数据 

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

insert into tb values('001' , null  , '广东省')

insert into tb values('002' , '001' , '广州市')

insert into tb values('003' , '001' , '深圳市')

insert into tb values('004' , '002' , '天河区')

insert into tb values('005' , '003' , '罗湖区')

insert into tb values('006' , '003' , '福田区')

insert into tb values('007' , '003' , '宝安区')

insert into tb values('008' , '007' , '西乡镇')

insert into tb values('009' , '007' , '龙华镇')

insert into tb values('010' , '007' , '松岗镇')

go

 

--创建用户定义函数 

create function f_cid(@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_cid(id) , '') from tb group by id

 

drop table tb

drop function f_cid

 

/*

id   children                               

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

001  001,002,003,004,005,006,007,008,009,010

002  002,004

003  003,005,006,007,008,009,010

004  004

005  005

006  006

007  007,008,009,010

008  008

009  009

010  010

 

（所影响的行数为 10 行）

*/

 

/*

标题：SQL SERVER 2000中查询指定节点及其所有父节点的函数(字符串形式显示)

 

*/

 

/*

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

insert into tb values('001' , null  , '广东省')

insert into tb values('002' , '001' , '广州市')

insert into tb values('003' , '001' , '深圳市')

insert into tb values('004' , '002' , '天河区')

insert into tb values('005' , '003' , '罗湖区')

insert into tb values('006' , '003' , '福田区')

insert into tb values('007' , '003' , '宝安区')

insert into tb values('008' , '007' , '西乡镇')

insert into tb values('009' , '007' , '龙华镇')

insert into tb values('010' , '007' , '松岗镇')

go

 

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

create function f_pid(@id varchar(3)) 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 is not null)

    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

 

/*

id   pid  name       路径                       

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

001  NULL 广东省     广东省

002  001  广州市     广东省,广州市

003  001  深圳市     广东省,深圳市

004  002  天河区     广东省,广州市,天河区

005  003  罗湖区     广东省,深圳市,罗湖区

006  003  福田区     广东省,深圳市,福田区

007  003  宝安区     广东省,深圳市,宝安区

008  007  西乡镇     广东省,深圳市,宝安区,西乡镇

009  007  龙华镇     广东省,深圳市,宝安区,龙华镇

010  007  松岗镇     广东省,深圳市,宝安区,松岗镇

 

（所影响的行数为 10 行）

*/

 

==================================================================

id-每个节结点的唯一标识，可以是流水号
Code-本级代码
pid-父节点的id
现在要求写出查询各个结点的select语句！希望各位高手给点建议

code     name  id code pid name
0
01           服装 1 01 0 服装
0101     男装 2 01 1 男装
010101     西装 3 01 2 西装
01010101     全毛 4 01 3 全毛
01010102     化纤 5 02 3 化纤
010102     休闲装 6 02 2 休闲装
0102     女装 7 02 1 女装
010201     套装 8 01 7 套装
010202     职业装 9 02 7 职业装
010203     休闲装 10 03 7 休闲装
010204       西装 11 04 7 西装
01020401     全毛 12 01 11 全毛
01020402     化纤 13 02 11 化纤
010205       休闲装 14 05 7 休闲装


 

 

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

--  Author : htl258(Tony)

--  Date   : 2010-04-23 02:37:24

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

--          Jul  9 2008 14:43:34 

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

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

--> 生成测试数据表:tb

 

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

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))

INSERT [tb]

SELECT 1,'01',0,N'服装' UNION ALL

SELECT 2,'01',1,N'男装' UNION ALL

SELECT 3,'01',2,N'西装' UNION ALL

SELECT 4,'01',3,N'全毛' UNION ALL

SELECT 5,'02',3,N'化纤' UNION ALL

SELECT 6,'02',2,N'休闲装' UNION ALL

SELECT 7,'02',1,N'女装' UNION ALL

SELECT 8,'01',7,N'套装' UNION ALL

SELECT 9,'02',7,N'职业装' UNION ALL

SELECT 10,'03',7,N'休闲装' UNION ALL

SELECT 11,'04',7,N'西装' UNION ALL

SELECT 12,'01',11,N'全毛' UNION ALL

SELECT 13,'02',11,N'化纤' UNION ALL

SELECT 14,'05',7,N'休闲装'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

 

;WITH T AS

(

    SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*,

        CAST(ROW_NUMBER() OVER(ORDER BY id) AS VARBINARY(MAX)) AS px 

    FROM tb AS A

    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid)

    UNION ALL 

    SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*,

         CAST(B.px+CAST(ROW_NUMBER() OVER(PARTITION BY B.pid ORDER BY A.id) AS BINARY(4)) 

               AS VARBINARY(MAX))    

    FROM tb AS A

        JOIN T AS B

    ON A.pid=B.id

)

SELECT Code,Name FROM T 

ORDER BY px

/*

Code                 Name

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

01                   服装

0101                 男装

010101               西装

01010101             全毛

01010102             化纤

010102               休闲装

0102                 女装

010201               套装

010202               职业装

010203               休闲装

010204               西装

01020401             全毛

01020402             化纤

010205               休闲装

 

(14 行受影响)

*/