sql server 通过上下级关系字段用递归方法生成上下级层级关联数据字段

一、场景

有一个数据库表里面存储了一个上下级关系的字段，通过存储这个觉得的上级id。如果要查找所有的上级或者下级就要通过循环每个去查找。如下

id	p_id	name
1	0	老板
2	1	副总
3	1	副总
4	2	项目总监
5	4	A项目经理
6	4	B项目经理
7	5	A1员工
8	5	A2员工
9	6	B1员工
10	6	B2员工

举例：如果要找所有10号员工的所有上级，就得一步一步循环上去？

二、解决方法

思路采用一个递归方法，先将层级结构用一个字段存储起来
表结构和数据

CREATE TABLE [dbo].[test] (
[id] int NOT NULL ,
[name] varchar(255) NULL ,
[pid] int NULL 
)
GO

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'1', N'老板', N'0')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'2', N'副总', N'1')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'3', N'副总', N'1')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'4', N'项目总监', N'2')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'5', N'A项目经理', N'4')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'6', N'B项目经理', N'4')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'7', N'A1员工', N'5')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'8', N'A2员工', N'5')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'9', N'B1员工', N'6')
GO
GO
INSERT INTO [dbo].[test] ([id], [name], [pid]) VALUES (N'10', N'B2员工', N'6')
GO
GO

**知识点**with as 是子查询模块，如果不了解可先去查询相关知识点
查询如下
找出所有的下级包括自己

WITH x as (
    select id,name,pid
    from test
    where id = 5
    union all
    select a.id,a.name,a.pid
    from test A
    join x B on A.pid = B.ID
)
select id,name,pid from x

结果

找出所有的上级包括自己

WITH x as (
    select id,name,pid
    from test
    where id = 5
    union all
    select a.id,a.name,a.pid
    from test A
    join x B on B.pid = A.ID
)
select id,name,pid from x

结果

三、进阶

如果每次查找都去循环查找，那层级多了就会影响性能，可以做如下相应的调整。给他一个标志上下级的关联字段，稍微修改一下表结构即可。
思路 ：用一个关联字段去关联上下级，每次增加修改删除的时候才需要排序，适合改动较少，查询较多的上下级表。比如上下级部门，上下级员工，这种查询都会多过修改，增加。
表结构
改变：添加一个上下级关联排序字段px

CREATE TABLE [dbo].[test] (
[id] int NOT NULL ,
[name] varchar(255) COLLATE Chinese_PRC_CI_AS NULL ,
[pid] int NULL ,
CONSTRAINT [PK__test__3213E83F7F60ED59] PRIMARY KEY ([id])
)
ON [PRIMARY]
GO

数据还是一样，只是px为空，由我们写一个更新px函数来更新
存储过程解析
- 第一步
先给所有节点排序，给上序号，并且先排好所有的一级节点

declare @n int
select @n=count(*) from menu

if OBJECT_ID('tempdb..#xtest') is not null drop table #xtest
select *
    ,right('0000000000'+cast(row_number()over(order by id)  as varchar(10)),len(@n)) rownum
into #xtest
from test

• 第二步

这一步是为了找出最大的父节点，作为父节点开始节点关联

    SELECT id,name,pid
    FROM #xtest AS A  
    WHERE NOT EXISTS(SELECT * FROM #xtest WHERE id=A.pid) 

• 第三步
  关联所有的子父节点

;WITH T AS  
(  
    SELECT A.id,A.name,A.pid
                        ,CAST(rownum AS varchar(MAX)) AS px
    FROM #xtest AS A  
    WHERE NOT EXISTS(SELECT * FROM #xtest WHERE id=A.pid)  

    UNION ALL   

    SELECT A.id,A.name,A.pid
                        , CAST(B.px+CAST(A.rownum AS varchar) AS varchar(MAX))
    FROM #xtest AS A  
        JOIN T AS B  
           ON A.pid=B.id  
)  
SELECT id,name,pid,px
into #test
FROM T   
ORDER BY id

• 第四步
  更新到表中

update a 
SET a.px = b.px
from test a ,#test b
where a.id = b.id

四、进阶答案

ALTER procedure [dbo].[var_update_test_px]
as 
BEGIN
declare @n int
select @n=count(*) from test

if OBJECT_ID('tempdb..#xtest') is not null drop table #xtest
select *
    ,right('0000000000'+cast(row_number()over(order by id)  as varchar(10)),len(@n)) rownum
into #xtest
from test

select * from #xtest

if OBJECT_ID('tempdb..#test') is not null drop table #test
;WITH T AS  
(  
    SELECT A.id,A.name,A.pid
                        ,CAST(rownum AS varchar(MAX)) AS px
    FROM #xtest AS A  
    WHERE NOT EXISTS(SELECT * FROM #xtest WHERE id=A.pid)  

    UNION ALL   

    SELECT A.id,A.name,A.pid
                        , CAST(B.px+CAST(A.rownum AS varchar) AS varchar(MAX))
    FROM #xtest AS A  
        JOIN T AS B  
           ON A.pid=B.id  
)  
SELECT id,name,pid,px
into #test
FROM T   
ORDER BY id


    begin try
        begin tran
            update a 
            SET a.px = b.px
            from test a ,#test b
            where a.id = b.id
        commit
    end try
    begin catch
        rollback
    end catch

END