使用树形结构与CTE实现父子列多层次查询

 

现在有一个表 tb

ID       NAME       PARENTID

1        A          0

2        A1         1

3        A2         1

4        B          0

5        B1         4

6        A11        2

7        A111       6

8        B11        5

9        A1111      7

10       A11111     9

这是一个动态树的实现，现在我想传入一个ID得到他所有父ID的名称

如我传入  2      显示 A
传入9  显示 A111,A11,A1,A
传入 8 显示  B1 B

这样写只可以根据当前的节点获取父节点的名称

SELECT NAME FROM A WHERE PARENTID = (SELECT PARENTID FROM A WHERE ID = @id)

 

主要用到BOM（双编号），字符串合并（函数）

 

if object_id('tb')is not null drop table tb

go

create table tb(ID int, NAME varchar(10),PARENTID int)

insert tb select

1,        'A'          ,0 union all select

2 ,       'A1'         ,1union all select

3  ,      'A2'         ,1union all select

4   ,     'B'          ,0union all select

5    ,    'B1'         ,4union all select

6     ,   'A11'       , 2union all select

7      ,  'A111'     ,  6union all select

8       , 'B11'     ,   5union all select

9        ,'A1111'  ,    7union all select

10       ,'A11111',     9

 

1、使用循环函数

if object_id('f_str')is not null drop function f_str

go

create function f_str(@id int)

returns varchar(20)

as

begin

    declare @str varchar(20)

    set @str=''

 

    while exists(select 1 from tb where id=@id)

    begin

        set @str=@str+','+(select name from tb where id=@id)

        set @id=(select PARENTID from tb where id=@id)

    end

    return stuff(@str,1,1,'')

end

go 

 

declare @id int

set @id=9

 

select dbo.f_str(@id) from tb where id=@id

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

A1111,A111,A11,A1,A

 

2、使用递归CTE

-- 查询指定部门所有上级部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = 'a111'

;WITH

tbs AS(

    -- 定位点成员

    SELECT * FROM tb

    WHERE name = @Dept_name

    UNION ALL

    -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

    SELECT A.*

    FROM tb A, tbS B

    WHERE (A.id= B.parentid) And (A.id<>0)

)

--SELECT * FROM tbs

SELECT STUFF((SELECT ','+NAME FROM tbs FOR XML PATH('') ),1,1,'')

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

A111,A11,A1,A

 

3、使用递归CTE函数

IF OBJECT_ID('FN_STR') IS NOT NULL

    DROP FUNCTION FN_STR

GO

CREATE FUNCTION FN_STR(@id INT)

RETURNS NVARCHAR(100)

AS

BEGIN

    DECLARE @s NVARCHAR(100);

    WITH t AS

    (

      SELECT * FROM tb WHERE id=@id

      UNION ALL

      SELECT a.* FROM tb a JOIN t b ON a.[ID]=b.[PARENTID]

    )

    SELECT @s=STUFF((SELECT ','+NAME FROM T WHERE id<>@id FOR XML PATH('')),1,1,'')

    RETURN @s

END

GO

 

SELECT dbo.FN_STR(9)

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

A1111,A111,A11,A1,A