递归求解，从下到上，但有点区别，不是排序！！！

Question

ID      No      Name          Level    ParentID 
1 11 A大学         0 0
2 01 A校区         1 1
3 0101 一教学楼 2 2
4 0102 二教学楼 2 2
5 0103 三教学楼 2 2
6 0104 实验楼         2 2
7 010101 1-1-01教室 3 3
8 010102 1-1-02教室 3 3
9 010201 2-1-01教室 3 4
11 010202 2-2-01教室 3 4
像上面这样的数据，如何才能变成下面的数据
ID      No      Name          Level    ParentID 
1 11 A大学         0 0
2 01 A校区         1 1
3 0101 一教学楼 2 2
4 0102 二教学楼 2 2
5 0103 三教学楼 2 2
6 0104 实验楼         2 2
7 010101 A校区一教学楼1-1-01教室 3 3
8 010102 A校区一教学楼1-1-02教室 3 3
9 010201 A校区二教学楼2-1-01教室 3 4
11 010202 A校区二教学楼2-2-01教室 3 4
也就是说如何将子节点前面父节点的Name加在它的前面，就是将父节点Name拼接到子节点上
但是第0级的就不用拼接到上面，试了一些办法都不怎么行希望高人给点想法

Answer 1

/*
标题：查询各节点的父路径函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)  
时间：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
goselect * , dbo.f_pid(id) 路径 from tb order by iddrop table tb
drop function f_pid

Answer 2

--子路径
DECLARE @T TABLE (ID INT,PID INT,Name VARCHAR(1))
INSERT INTO @T
SELECT 1,null,'A' UNION ALL
SELECT 2,1,'B' UNION ALL
SELECT 3,1,'C' UNION ALL
SELECT 4,3,'D' UNION ALL
SELECT 5,4,'E'--SQL查询如下:;WITH Liang AS
(
    SELECT
        *,
        PATH=CAST(Name AS VARCHAR(MAX))
    FROM @T
    WHERE PID IS NULL
    UNION ALL
    SELECT
        B.*,
        A.PATH+'.'+B.Name
    FROM Liang AS A
        JOIN @T AS B
            ON B.PID=A.ID
)
SELECT *
FROM Liang/*
ID          PID         Name PATH
----------- ----------- ---- --------------------------
1           NULL        A    A
2           1           B    A.B
3           1           C    A.C
4           3           D    A.C.D
5           4           E    A.C.D.E(5 行受影响)
*/

Answer 3

谢谢 爱新觉罗.毓华 ，下面的那个我还用不上，就是with as的，我用的还是2K，我看看你上面的那个，谢谢了

Answer 4

2K的话得用游标或者WIHLE循环拼接，觉得不难啊，楼主多想想。就出来了

Answer 5

create table tb(ID int,[No] nvarchar(20),[Name] nvarchar(20),[Level] int,ParentID int)
insert into tb select
1 ,'11' ,'A大学',         0, 0 union all select 
2 ,'01' ,'A校区',         1, 1 union all select
3 ,'0101', '一教学楼', 2 ,2 union all select
4 ,'0102', '二教学楼', 2 ,2 union all select
5 ,'0103','三教学楼', 2 ,2 union all select
6 ,'0104', '实验楼',       2, 2 union all select
7 ,'010101', '1-1-01教室', 3, 3 union all select
8 ,'010102', '1-1-02教室', 3, 3 union all select
9 ,'010201', '2-1-01教室', 3, 4 union all select
11, '010202', '2-2-01教室', 3, 4  
;with cte
as
(
select tb.ID,tb.[No],tb.[Name],tb.[Level],tb.ParentID from tb where parentID=0
union all
select tb.ID,tb.[No],[Name]=cast((cte.[name]+tb.[Name]) as nvarchar(20)),tb.[Level],tb.ParentID from tb
inner join cte on tb.parentid=cte.id
)select * from cte 
/*ID          No                   Name                 Level       ParentID
----------- -------------------- -------------------- ----------- -----------
1           11                   A大学                  0           0
2           01                   A大学A校区               1           1
3           0101                 A大学A校区一教学楼           2           2
4           0102                 A大学A校区二教学楼           2           2
5           0103                 A大学A校区三教学楼           2           2
6           0104                 A大学A校区实验楼            2           2
9           010201               A大学A校区二教学楼2-1-01教室   3           4
11          010202               A大学A校区二教学楼2-2-01教室   3           4
7           010101               A大学A校区一教学楼1-1-01教室   3           3
8           010102               A大学A校区一教学楼1-1-02教室   3           3(10 行受影响)
*/

Answer 6

其实更简单一些呢就是取所有教室的名字，但这个name必须是跟父节点的拼接，除了pid为0外的所有

Answer 7

6楼这个方法很好，但是很可惜，我的还是2K，所以还不能用这个with as。。

Answer 8

类似这个吧, 适用SQL2000IF OBJECT_ID('dbo.Employees') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL PRIMARY KEY,
  mgrid   INT         NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  CHECK (empid <> mgrid)
);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(1, NULL, 'David', $10000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(2, 1, 'Eitan', $7000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(3, 1, 'Ina', $7500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(4, 2, 'Seraph', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(5, 2, 'Jiru', $5500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(6, 2, 'Steve', $4500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(7, 3, 'Aaron', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(8, 5, 'Lilach', $3500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(9, 7, 'Rita', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(10, 5, 'Sean', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(11, 7, 'Gabriel', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(12, 9, 'Emilia' , $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(13, 9, 'Michael', $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(14, 9, 'Didi', $1500.00);
--创建索引
CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);
go
IF OBJECT_ID('dbo.fn_subordinates3') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates3;
GO
CREATE FUNCTION dbo.fn_subordinates3
  (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
  empid INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT          NOT NULL,
  path  VARCHAR(900) NOT NULL
  UNIQUE CLUSTERED(lvl, empid) 
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                  SET @maxlevels = COALESCE(@maxlevels, 2147483647);
  INSERT INTO @Subs(empid, lvl, path)
    SELECT empid, @lvl, CAST(empname AS VARCHAR(100))--ZHU YI ZHE LI 
    FROM dbo.Employees 
    WHERE empid = @root;  WHILE @@rowcount > 0          
    AND @lvl < @maxlevels       
  BEGIN
    SET @lvl = @lvl + 1;       
    INSERT INTO @Subs(empid, lvl, path)
      SELECT C.empid, @lvl,
        P.path +'-'+ CAST(C.empname AS VARCHAR(100)) --和上面类型保持一致
      FROM @Subs AS P           
        JOIN dbo.Employees AS C 
          ON P.lvl = @lvl - 1  
          AND C.mgrid = P.empid;
  END  RETURN;
END
GO
SELECT empid,  path
FROM dbo.fn_subordinates3(1, NULL) AS S
ORDER BY PATH 
DROP FUNCTION dbo.fn_subordinates3;
/*empid       path
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           David
2           David-Eitan
5           David-Eitan-Jiru
8           David-Eitan-Jiru-Lilach
10          David-Eitan-Jiru-Sean
4           David-Eitan-Seraph
6           David-Eitan-Steve
3           David-Ina
7           David-Ina-Aaron
11          David-Ina-Aaron-Gabriel
9           David-Ina-Aaron-Rita
14          David-Ina-Aaron-Rita-Didi
12          David-Ina-Aaron-Rita-Emilia
13          David-Ina-Aaron-Rita-Michael

Answer 9

我要的最后的结果其实就是这个
ID  No              Name          Level ParentID 
7  010101  A校区一教学楼1-1-01教室  3  3
8  010102  A校区一教学楼1-1-02教室  3  3
9  010201  A校区二教学楼2-1-01教室  3  4
11 010202  A校区二教学楼2-2-01教室  3  4 

Answer 10

create table tb(ID int,[No] nvarchar(20),[Name] nvarchar(20),[Level] int,ParentID int)
insert into tb select
1 ,'11' ,'A大学',         0, 0 union all select 
2 ,'01' ,'A校区',         1, 1 union all select
3 ,'0101', '一教学楼', 2 ,2 union all select
4 ,'0102', '二教学楼', 2 ,2 union all select
5 ,'0103','三教学楼', 2 ,2 union all select
6 ,'0104', '实验楼',       2, 2 union all select
7 ,'010101', '1-1-01教室', 3, 3 union all select
8 ,'010102', '1-1-02教室', 3, 3 union all select
9 ,'010201', '2-1-01教室', 3, 4 union all select
11, '010202', '2-2-01教室', 3, 4  
if object_ID('f_pid') is not null
drop function f_pid
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 ParentID <> 0)
    begin
      select @id = b.id , @re_str = b.name + '' + @re_str from tb a , tb b where a.id = @id and a.ParentID = b.id
    end
  return @re_str
end
goselect * , dbo.f_pid(id) 路径 from tb order by id--;with cte
--as
--(
-- select tb.ID,tb.[No],tb.[Name],tb.[Level],tb.ParentID from tb where parentID=0
-- union all
-- select tb.ID,tb.[No],[Name]=cast((cte.[name]+tb.[Name]) as nvarchar(20)),tb.[Level],tb.ParentID from tb
-- inner join cte on tb.parentid=cte.id
--)
/*ID          No                   Name                 Level       ParentID    路径
----------- -------------------- -------------------- ----------- ----------- ----------------------------------------------------------------------------------------------------
1           11                   A大学                  0           0           A大学
2           01                   A校区                  1           1           A大学A校区
3           0101                 一教学楼                 2           2           A大学A校区一教学楼
4           0102                 二教学楼                 2           2           A大学A校区二教学楼
5           0103                 三教学楼                 2           2           A大学A校区三教学楼
6           0104                 实验楼                  2           2           A大学A校区实验楼
7           010101               1-1-01教室             3           3           A大学A校区一教学楼1-1-01教室
8           010102               1-1-02教室             3           3           A大学A校区一教学楼1-1-02教室
9           010201               2-1-01教室             3           4           A大学A校区二教学楼2-1-01教室
11          010202               2-2-01教室             3           4           A大学A校区二教学楼2-2-01教室(10 行受影响)*/

Answer 11

create table tb(ID int,[No] nvarchar(10),[Name] nvarchar(10),[Level] int,ParentID int)
insert into tb select
1 ,'11' ,'A大学',         0, 0 union all select 
2 ,'01' ,'A校区',         1, 1 union all select
3 ,'0101', '一教学楼', 2 ,2 union all select
4 ,'0102', '二教学楼', 2 ,2 union all select
5 ,'0103','三教学楼', 2 ,2 union all select
6 ,'0104', '实验楼',       2, 2 union all select
7 ,'010101', '1-1-01教室', 3, 3 union all select
8 ,'010102', '1-1-02教室', 3, 3 union all select
9 ,'010201', '2-1-01教室', 3, 4 union all select
11, '010202', '2-2-01教室', 3, 4  
if object_ID('f_pid') is not null
drop function f_pid
go
create function f_pid(@id int) returns varchar(30)
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 ParentID <> 0)
    begin
      select @id = b.id , @re_str = b.name + '' + @re_str from tb a , tb b where a.id = @id and a.ParentID = b.id
    end
  return @re_str
end
goselect tb.ID,tb.[No],[Name]=dbo.f_pid(id),tb.[Level],tb.ParentID from tb
where id not in(select parentid from tb) order by id/*ID          No         Name                           Level       ParentID
----------- ---------- ------------------------------ ----------- -----------
5           0103       A大学A校区三教学楼                     2           2
6           0104       A大学A校区实验楼                      2           2
7           010101     A大学A校区一教学楼1-1-01教室             3           3
8           010102     A大学A校区一教学楼1-1-02教室             3           3
9           010201     A大学A校区二教学楼2-1-01教室             3           4
11          010202     A大学A校区二教学楼2-2-01教室             3           4(6 行受影响)*/

Answer 12

总算出结果了if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[No] varchar(6),[Name] varchar(10),[Level] int,[ParentID] int)
insert [tb]
select 1,'11','A大学',0,0 union all
select 2,'01','A校区',1,1 union all
select 3,'0101','一教学楼',2,2 union all
select 4,'0102','二教学楼',2,2 union all
select 5,'0103','三教学楼',2,2 union all
select 6,'0104','实验楼',2,2 union all
select 7,'010101','1-1-01教室',3,3 union all
select 8,'010102','1-1-02教室',3,3 union all
select 9,'010201','2-1-01教室',3,4 union all
select 11,'010202','2-2-01教室',3,4
IF OBJECT_ID('dbo.fn_subordinates3') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates3;
GO
CREATE FUNCTION dbo.fn_subordinates3
  (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
  ID INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT          NOT NULL,
  path  VARCHAR(900) NOT NULL
  UNIQUE CLUSTERED(lvl, ID) 
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                  SET @maxlevels = COALESCE(@maxlevels, 2147483647);
  INSERT INTO @Subs(ID, lvl, path)
    SELECT ID, @lvl, CAST(Name AS VARCHAR(100))--ZHU YI ZHE LI 
    FROM tb 
    WHERE ID = @root;  WHILE @@rowcount > 0          
    AND @lvl < @maxlevels       
  BEGIN
    SET @lvl = @lvl + 1;       
    INSERT INTO @Subs(ID, lvl, path)
      SELECT C.ID, @lvl,
        P.path +'-'+ CAST(C.Name AS VARCHAR(100)) --和上面类型保持一致
      FROM @Subs AS P           
        JOIN tb AS C 
          ON P.lvl = @lvl - 1  
          AND C.ParentID = P.ID;
  END  RETURN;
END
GO
SELECT ID,  path
FROM dbo.fn_subordinates3(1, NULL) AS S
ORDER BY PATH 
DROP FUNCTION dbo.fn_subordinates3;
DROP TABLE TB/*
ID          path
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           A大学
2           A大学-A校区
4           A大学-A校区-二教学楼
9           A大学-A校区-二教学楼-2-1-01教室
11          A大学-A校区-二教学楼-2-2-01教室
5           A大学-A校区-三教学楼
6           A大学-A校区-实验楼
3           A大学-A校区-一教学楼
7           A大学-A校区-一教学楼-1-1-01教室
8           A大学-A校区-一教学楼-1-1-02教室

Answer 13

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[No] varchar(6),[Name] varchar(10),[Level] int,[ParentID] int)
insert [tb]
select 1,'11','A大学',0,0 union all
select 2,'01','A校区',1,1 union all
select 3,'0101','一教学楼',2,2 union all
select 4,'0102','二教学楼',2,2 union all
select 5,'0103','三教学楼',2,2 union all
select 6,'0104','实验楼',2,2 union all
select 7,'010101','1-1-01教室',3,3 union all
select 8,'010102','1-1-02教室',3,3 union all
select 9,'010201','2-1-01教室',3,4 union all
select 11,'010202','2-2-01教室',3,4
IF OBJECT_ID('dbo.fn_subordinates3') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates3;
GO
CREATE FUNCTION dbo.fn_subordinates3
  (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
  ID INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT          NOT NULL,
  path  VARCHAR(900) NOT NULL
  UNIQUE CLUSTERED(lvl, ID) 
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                  SET @maxlevels = COALESCE(@maxlevels, 2147483647);
  INSERT INTO @Subs(ID, lvl, path)
    SELECT ID, @lvl, CAST(Name AS VARCHAR(100))--ZHU YI ZHE LI 
    FROM tb 
    WHERE ID = @root;  WHILE @@rowcount > 0          
    AND @lvl < @maxlevels       
  BEGIN
    SET @lvl = @lvl + 1;       
    INSERT INTO @Subs(ID, lvl, path)
      SELECT C.ID, @lvl,
        P.path +'-'+ CAST(C.Name AS VARCHAR(100)) --和上面类型保持一致
      FROM @Subs AS P           
        JOIN tb AS C 
          ON P.lvl = @lvl - 1  
          AND C.ParentID = P.ID;
  END  RETURN;
END
GO
SELECT ID,  lvl, path
FROM dbo.fn_subordinates3(1, NULL) AS S
where lvl=3
ORDER BY PATH 
DROP FUNCTION dbo.fn_subordinates3;
DROP TABLE TB/*
ID          lvl         path
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9           3           A大学-A校区-二教学楼-2-1-01教室
11          3           A大学-A校区-二教学楼-2-2-01教室
7           3           A大学-A校区-一教学楼-1-1-01教室
8           3           A大学-A校区-一教学楼-1-1-02教室(4 row(s) affected)

Answer 14

Ok，谢谢大家了我明白该怎么做了，Thk！！！！