遍历树的性能问题,大家给个优化方案.

Question

自己写了个函数,性能实在太低.select * from fn_ReportDept(6)这个查询要9秒,数据共有1000条/*查询某层级下的所有部门,前四个字段(depart_id varchar(16),depart_code varchar(30),depart_name varchar(100),_level int  为本部门的信息,,后三字段main_id varchar(16),main_code varchar(30),main_name varchar(100)
为该部门所在层级上的父部门信息如 该部门的层级为7  而参数@level为3  ,则后三字段找该部门在层级为3上的父部门资料
*/create function fn_ReportDept(@level int)
returns @rpt_dept 
table(depart_id varchar(16),depart_code varchar(30),depart_name varchar(100),_level int
,main_id varchar(16),main_code varchar(30),main_name varchar(100))
 as 
  begin
  declare @depart_id varchar(16),@_level int
  insert into @rpt_dept(depart_id,depart_code,depart_name,_level) select a.hrids,a.depart_code,a.depart_name,b.rl 
  from hr_depart_data a,g3_body_d001 b where a.hrids=b.hiids and b.rl>=@level 
  --查询部门在@level层级上的部门
    declare _cur cursor for select depart_id,_level from @rpt_dept 
    open _cur
    fetch next from _cur into @depart_id,@_level
    if @_level>@level 
        update @rpt_dept set main_id=(select dbo.fn_GetPidByLevel(@depart_id,@level)) where depart_id=@depart_id
    while @@fetch_status=0
       begin
          fetch next from _cur into @depart_id,@_level
          if @_level>@level 
      update @rpt_dept set main_id=(select dbo.fn_GetPidByLevel(@depart_id,@level)) where depart_id=@depart_id
       end
    close _cur
    deallocate _cur
    --更新部门代码及名称
    update @rpt_dept set main_code=(select depart_code from hr_depart_data where hrids=main_id),
    main_name=(select depart_name from hr_depart_data where hrids=main_id)
    update @rpt_dept set main_id=depart_id,main_code=depart_code,main_name=depart_name where main_id is null
    return 
 end
--找某个部门在某个层级上的父部门ID
CREATE FUNCTION fn_GetPidByLevel(@ID varchar(16),@level int)
RETURNS VARCHAR(16) 
AS
BEGIN
    DECLARE @_LEVEL INT  --部门层级
    DECLARE @PID VARCHAR(16) --父部门ID
    --HIIDS为本部门ID,RL 为该部门层级,DEPARTC 为该部门上级部门
    declare _CUR cursor for SELECT HIIDS,RL FROM G3_BODY_D001 WHERE HIIDS=(SELECT DEPARTC FROM G3_BODY_D001 WHERE HIIDS=@ID) 
    OPEN _CUR
    FETCH NEXT FROM _CUR INTO @PID,@_LEVEL
    IF @_LEVEL>@level
       SET @PID = dbo.fn_GetPidByLevel(@PID,@level)
    CLOSE _CUR
    DEALLOCATE _CUR
RETURN @PID
END

Answer 1

--测试数据
if OBJECT_ID('tb') is not null 
drop table tb 
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO
--2000的方法--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
declare @Level int
set @level=1
insert @t_level select @id,@level
while @@rowcount>0
begin 
    set @level=@level+1
    insert @t_Level select tb.id,@level
     from tb join @t_level t on tb.pid=t.id
     where t.level+1=@level
   end
   return
endselect tb.*
from tb join dbo.f_cid('002') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
002  001  烟台市
004  002  招远市*/
go
--2005的方法（CTE）declare @n varchar(10)
set @n='002'
;with 
jidian as
(
  select * from tb where ID=@n
  union all
  select t.* from jidian j join tb t on j.ID=t.PID
)
select * from jidian 
go
/*
ID   PID  Name
---- ---- ----------
002  001  烟台市
004  002  招远市
*/
go
--查找指定节点的所有父节点(标准树形,即一个子节点只有一个父节点)
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
END
RETURN
END
select tb.*
from tb join dbo.f_Pid('004') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
001  NULL 山东省
002  001  烟台市
004  002  招远市
*/
go
--2005的方法
declare @n varchar(10)
set @n='004'
;with
fujidian as
(
 select * from tb where ID=@n and PID is not null
 union all
 select a.* from tb a join fujidian f on a.ID=f.PID 
)
select * from fujidian order by ID
/*
ID   PID  Name
---- ---- ----------
001  NULL 山东省
002  001  烟台市
004  002  招远市
*/

Answer 2

--测试数据
if OBJECT_ID('tb') is not null 
drop table tb 
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO
--2000的方法--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
declare @Level int
set @level=1
insert @t_level select @id,@level
while @@rowcount>0
begin 
    set @level=@level+1
    insert @t_Level select tb.id,@level
     from tb join @t_level t on tb.pid=t.id
     where t.level+1=@level
   end
   return
endselect tb.*
from tb join dbo.f_cid('002') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
002  001  烟台市
004  002  招远市*/
go
--2005的方法（CTE）declare @n varchar(10)
set @n='002'
;with 
jidian as
(
  select * from tb where ID=@n
  union all
  select t.* from jidian j join tb t on j.ID=t.PID
)
select * from jidian 
go
/*
ID   PID  Name
---- ---- ----------
002  001  烟台市
004  002  招远市
*/
go
--查找指定节点的所有父节点(标准树形,即一个子节点只有一个父节点)
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
    INSERT @t_Level SELECT @ID
    SELECT @ID=PID FROM tb
    WHERE ID=@ID
        AND PID IS NOT NULL
    WHILE @@ROWCOUNT>0
    BEGIN
        INSERT @t_Level SELECT @ID
        SELECT @ID=PID FROM tb
        WHERE ID=@ID
            AND PID IS NOT NULL
    END
    RETURN
END
select tb.*
from tb join dbo.f_Pid('004') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
001  NULL 山东省
002  001  烟台市
004  002  招远市
*/
go
--2005的方法
declare @n varchar(10)
set @n='004'
;with
fujidian as
(
 select * from tb where ID=@n and PID is not null
 union all
 select a.* from tb a join fujidian f on a.ID=f.PID 
)
select * from fujidian order by ID
/*
ID   PID  Name
---- ---- ----------
001  NULL 山东省
002  001  烟台市
004  002  招远市
*/

Answer 3

参考如下:
/*
标题：查询指定节点及其所有父节点的函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/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 @t_level table(id varchar(3))
as
begin
  insert into @t_level select @id
  select @id = pid from tb where id = @id and pid is not null
  while @@ROWCOUNT > 0
  begin
    insert into @t_level select @id 
    select @id = pid from tb where id = @id and pid is not null
  end
  return
end
go--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市（所影响的行数为 2 行）
*/--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市
007  003  宝安区
008  007  西乡镇（所影响的行数为 4 行）
*/drop table tb
drop function f_pid@@ROWCOUNT：返回受上一语句影响的行数。
返回类型：integer。
注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。
示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
   print 'Warning: No rows were updated'结果：(所影响的行数为 0 行)
Warning: No rows were updated

Answer 4

还试觉得
我的这个好static DataColumn column = new DataColumn();   
  static DataTable table = new DataTable();   
  
    static DataRow MyRow;   
ublic static DataTable GetCategoryTree(WebInfoBase info)   
  {   
     DataTable dt = info.List(_DefaultDB, "", "[ID],[Title],[ParentID],[ChildNum],[Depth],[OrderNo]", "&version=", "[ID] ASC");   
       
     if (table.Rows.Count > 0)   
     {   
         table.Columns.Clear();   
         table.Rows.Clear();   
     }   
     CreateDataTable();   
     GetTree(dt, "0", 0);   
     return table;   
  }   
    public static void GetTree(DataTable dt, string pid, int blank)   
    {   
        string str = " ";   
        DataView dv = new DataView(dt);   
        dv.RowFilter = "ParentID = " + pid;   
        if (blank > 0)   
        {   
            string s = "";   
            if (blank == 1)   
            {   
                str = "├";   
            }   
            for (int i = 2; i <= blank; i++)   
            {   
                s = s + "  |  "+" "+" - ";   
            }   
            str = s + "├";   
        }   
           
        foreach (DataRowView drv in dv)   
        {   
            string id = drv["ID"].ToString();   
            string Title = drv["Title"].ToString();   
            string OrderNo = drv["OrderNo"].ToString();   
            string ParentID = drv["ParentID"].ToString();   
            string Depth = drv["Depth"].ToString();   
            string ChildNum = drv["ChildNum"].ToString();   
               
            MyRow = table.NewRow();   
            MyRow["ID"] = int.Parse(id);   
            MyRow["Title"] = str + Title;   
            MyRow["OrderNo"] = int.Parse(OrderNo);   
            MyRow["ParentID"] = int.Parse(ParentID);   
            MyRow["Depth"] = int.Parse(Depth);   
            MyRow["ChildNum"] = int.Parse(ChildNum);   
            table.Rows.Add(MyRow);   
  
            int n = int.Parse(Depth);   
            //if (n <= 1)   
            //{   
                n++;   
            //}   
            GetTree(dt, id, n);   
        }   
    }   
       public static void CreateDataTable()   
    {   
        table.Columns.Clear();   
        column = new DataColumn();   
        column.DataType = System.Type.GetType("System.Int32");   
        column.ColumnName = "ID";   
        table.Columns.Add(column);   
  
        column = new DataColumn();   
        column.DataType = System.Type.GetType("System.Int32");   
        column.ColumnName = "ParentID";   
        table.Columns.Add(column);   
  
        column = new DataColumn();   
        column.DataType = Type.GetType("System.String");   
        column.ColumnName = "Title";   
        table.Columns.Add(column);   
  
        column = new DataColumn();   
        column.DataType = Type.GetType("System.Int32");   
        column.ColumnName = "ChildNum";   
        table.Columns.Add(column);   
  
        column = new DataColumn();   
        column.DataType = Type.GetType("System.Int32");   
        column.ColumnName = "Depth";   
        table.Columns.Add(column);   
  
        column = new DataColumn();   
        column.DataType = Type.GetType("System.Int32");   
        column.ColumnName = "OrderNo";   
        table.Columns.Add(column);   
        //table.Columns.Clear();   
  
    }   
   
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/01/21/3846843.aspx

Answer 5

2005以上用CTE递归.层次多了也不行.

Answer 6

如果是SQL2008,可以使用hierarchyid类型来设计具有树型层次关系的表
http://blog.csdn.net/htl258/archive/2009/08/04/4404641.aspx

Answer 7

性能全在fn_GetPidByLevel这个函数里..select * from fn_ReportDept(2) 层级越小,每笔去找父ID的递归就越深..性能就下降厉害..数据库是2005的...我想要在fn_GetPidByLevel这个函数里不去查询递归,,而去重复利用游标,在游标里去判断取值.不知是否可行..

Answer 8

1000条数据？？那用上面他们贴的那些function ，肯定在1秒之内就查出来了

Answer 9

他们贴的那些根本和我写的对不上,不符合我的逻辑
看这句
update @rpt_dept set main_id=(select dbo.fn_GetPidByLevel(@depart_id,@level)) where depart_id=@depart_idfn_GetPidByLevel(@depart_id,@level) 这个在递归啊...这是要死人的东东

Answer 10

改改你的表加个字段记录该部门的全路径
比如：
    A  为一级部门 id=1 全路径为000001
     B 为A的子部门 id=22 全路径为000001000022查询时直接排序这个字段就可以得到近似树结构了

Answer 11

好像大家还没看懂我的逻辑..也许是我表述不清吧..我的逻辑主函数fn_ReportDept,但该函数会调用另一个被调函数fn_GetPidByLevel,
该函数fn_GetPidByLevel(@depart_id,@level) 是否返回父部门的(某部门在某层级上的部门,只有一笔)数据样例原数据
部门ID    部门代号      部门名称    父部门ID 层级
depart_id depart_code  depart_name  departc dl
    1     code1         name1          0     0
    2     code2         name2          1     1
    3     code3         name3          2     2
    4     code4         name4          3     3
    5     code5         name5          4     4
    6     code6         name6          5     5
        
调用函数
select fn_ReportDept(2)
查询后的数据:
depart_id depart_code depart_name level main_id main_code main_name
   6         code6        name6     5     3         code3  name3

Answer 12

还是没看懂, 你给的样本数据, 应该是下面的树状:1    code1        name1          0    0
-------------------------------------------
      2    code2        name2          1    1
      ------------------------------------------
                    3    code3        name3          2    2
                -------------------------------------------                    
                            4    code4        name4          3    3
                            -------------------------------------------
                                    5    code5        name5          4    4
                                    -------------------------------------------
                                            6    code6        name6          5    5
                                            ------------------------------------------- code1是根, 下一级只有一个子节点code2
code2的level是1, 下一级只有一个子节点code3 
code3的level是2, 下一级只有一个子节点code4
code4的level是3, 下一级只有一个子节点code5
code5的level是4, 下一级只有一个子节点code6
code6的level是5, 没有下一级子节点.调用函数select fn_ReportDept(2)
我怎么觉得查询后的数据应该是:
depart_id depart_code depart_name level main_id main_code main_name
  4        code4        name4    3    3        code3  name3 
  5        code5        name5    4    3        code3  name3 
  6        code6        name6    5    3        code3  name3 因为, code4,code5,code6 都是code3的下属节点, 子节点, 孙节点,............难道fn_GetPidByLevel(@depart_id,@level)只返回下属节点中level最大的一个?(我只是猜测)能再详细点描述下逻辑否?

Answer 13

不好意思,,我查询后的数据贴错了..
你贴出来的差不多是对的,只不过还要加在@level那一级的部门,不过这一层级没去调用fn_GetPidByLevel
也可以不算应该是这样的
depart_id depart_code depart_name level main_id main_code main_name
  3        code3        name3    3    3        code3  name3
  4        code4        name4    3    3        code3  name3
  5        code5        name5    4    3        code3  name3
  6        code6        name6    5    3        code3  name3 
终于有一人正式关注了.
谢谢!

Answer 14

试试create function fn_ReportDept(@level int)
returns @rpt_dept table(
depart_id varchar(16),
depart_code varchar(30),
depart_name varchar(100),
_level int,
main_id varchar(16),
main_code varchar(30),
main_name varchar(100)
)
as 
begin
declare @depart_id varchar(16),@_level int;

insert into @rpt_dept(depart_id,depart_code,depart_name,_level,main_id) 
select a.hrids,a.depart_code,a.depart_name,b.rl , a.depart_code
from hr_depart_data as a, g3_body_d001 as b where a.hrids=b.hiids and b.rl=@level;

insert into @rpt_dept(depart_id,depart_code,depart_name,_level,main_id) 
select a.hrids,a.depart_code,a.depart_name,b.rl ,dbo.fn_GetPidByLevel(a.depart_code,@level) 
from hr_depart_data as a, g3_body_d001 as b where a.hrids=b.hiids and b.rl>@level;

update @rpt_dept set main_code=(select depart_code from hr_depart_data where hrids=main_id),
main_name=(select depart_name from hr_depart_data where hrids=main_id) ;

/*
--查询部门在@level层级上的部门
declare _cur cursor LOCAL FAST_FORWARD for select depart_id,_level from @rpt_dept and _level=@level ;
open _cur ;
fetch next from _cur into @depart_id, @_level ;
if @_level>@level
update @rpt_dept set main_id=(select dbo.fn_GetPidByLevel(@depart_id,@level)) where depart_id=@depart_id
while @@fetch_status=0
begin


fetch next from _cur into @depart_id,@_level
if @_level>@level
update @rpt_dept set main_id=(select dbo.fn_GetPidByLevel(@depart_id,@level)) where depart_id=@depart_id
end
close _cur
deallocate _cur
--更新部门代码及名称
update @rpt_dept set main_code=(select depart_code from hr_depart_data where hrids=main_id),
main_name=(select depart_name from hr_depart_data where hrids=main_id)
update @rpt_dept set main_id=depart_id,main_code=depart_code,main_name=depart_name where main_id is null
*/
return
end
--找某个部门在某个层级上的父部门ID
CREATE FUNCTION fn_GetPidByLevel(@ID varchar(16),@level int)
RETURNS VARCHAR(16) 
AS
BEGIN
    DECLARE @_LEVEL INT  --部门层级
    DECLARE @PID VARCHAR(16) --父部门ID
    --HIIDS为本部门ID,RL 为该部门层级,DEPARTC 为该部门上级部门
    
    DECLARE @TID varchar(16), @TPID varchar(16) ;
    set @TID = @ID ;
    
    while exists(SELECT @TPID = HIIDS, @_LEVEL = RL FROM G3_BODY_D001 WHERE HIIDS=(SELECT DEPARTC FROM G3_BODY_D001 WHERE HIIDS=@TID))
    begin
--SELECT @TPID = HIIDS, @_LEVEL = RL FROM G3_BODY_D001 WHERE HIIDS=(SELECT DEPARTC FROM G3_BODY_D001 WHERE HIIDS=@TID) ;
     IF @_LEVEL=@level
     begin
     set @PID = @TPID ;
     break;
     end
   set @TID = @TPID ;
    end
    
    /*
    declare _CUR cursor for SELECT HIIDS,RL FROM G3_BODY_D001 WHERE HIIDS=(SELECT DEPARTC FROM G3_BODY_D001 WHERE HIIDS=@ID) 
    OPEN _CUR
    FETCH NEXT FROM _CUR INTO @PID,@_LEVEL
    IF @_LEVEL>@level
       SET @PID = dbo.fn_GetPidByLevel(@PID,@level)
    CLOSE _CUR
    DEALLOCATE _CUR
    */
    RETURN @PID
END

Answer 15

上面的递推判断有错,改一下
create function fn_ReportDept(@level int)
returns @rpt_dept table(
    depart_id varchar(16),
    depart_code varchar(30),
    depart_name varchar(100),
    _level int,
    main_id varchar(16),
    main_code varchar(30),
    main_name varchar(100)
)
as 
begin
    declare @depart_id varchar(16),@_level int;
    
    insert into @rpt_dept(depart_id,depart_code,depart_name,_level,main_id) 
        select a.hrids,a.depart_code,a.depart_name,b.rl , a.depart_code
            from hr_depart_data as a, g3_body_d001 as b where a.hrids=b.hiids and b.rl=@level;
            
    insert into @rpt_dept(depart_id,depart_code,depart_name,_level,main_id) 
        select a.hrids,a.depart_code,a.depart_name,b.rl ,dbo.fn_GetPidByLevel(a.depart_code,@level) 
            from hr_depart_data as a, g3_body_d001 as b where a.hrids=b.hiids and b.rl>@level;
            
    update @rpt_dept set main_code=(select depart_code from hr_depart_data where hrids=main_id),
        main_name=(select depart_name from hr_depart_data where hrids=main_id) ;
    
   
    return
end
--找某个部门在某个层级上的父部门ID
CREATE FUNCTION fn_GetPidByLevel(@ID varchar(16),@level int)
RETURNS VARCHAR(16) 
AS
BEGIN
    DECLARE @_LEVEL INT  --部门层级
    DECLARE @PID VARCHAR(16) --父部门ID
    --HIIDS为本部门ID,RL 为该部门层级,DEPARTC 为该部门上级部门
    
    DECLARE @TID varchar(16), @TPID varchar(16) ;
    set @TID = @ID ;
    
    while exists(SELECT 1 FROM G3_BODY_D001 WHERE HIIDS=(SELECT DEPARTC FROM G3_BODY_D001 WHERE HIIDS=@TID))
    begin
     SELECT @TPID = HIIDS, @_LEVEL = RL FROM G3_BODY_D001 WHERE HIIDS=(SELECT DEPARTC FROM G3_BODY_D001 WHERE HIIDS=@TID) ;
        IF @_LEVEL=@level
        begin
            set @PID = @TPID ;
            break;
        end
      set @TID = @TPID ;
    end
    
    
    RETURN @PID
END

Answer 16

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-03 17:47:36.077●●●●●
　★★★★★soft_wsx★★★★★
*/
--树型结构处理之双编号（广度深度排序）
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0 
  drop table tb
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000))
insert tb
select '0001',null,'云南省'
union all select '0002','0001','昆明市'
union all select '0003','0001','昭通市'
union all select '0009','0001','大理市'
union all select '0008',null,'四川省'
union all select '0004',null,'贵州省'
union all select '0005','0002','五华区'
union all select '0007','0002','水富县'
union all select '0006','0005','西园路192号'
union all select '0010','0006','金色梧桐'
union all select '0011','0010','科技有限公司'
union all select '0015','0007','两碗乡'
union all select '0013','0015','两碗村'
union all select '0012','0013','某跨国集团董事长'
union all select '0014','0008','成都市'--select * from tb
--广度排序(先显示第一层节点，再显示第二次节点......)
--定义辅助表
declare @level_tb table(bh nvarchar(10),level int)
declare @level int
set @level=0
insert @level_tb(bh,level)
select ybh,@level from tb where ebh is null
while @@ROWCOUNT>0
  begin
    set @level=@level+1
    insert @level_tb(bh,level)
      select ybh,@level 
        from tb a,@level_tb b
        where a.ebh=b.bh 
              and b.level=@level-1
  end 
  select a.*,b.* from tb a,@level_tb b where a.ybh=b.bh order by level
/*
ybh ebh beizhu bh level
0001 NULL 云南省 0001 0
0008 NULL 四川省 0008 0
0004 NULL 贵州省 0004 0
0002 0001 昆明市 0002 1
0003 0001 昭通市 0003 1
0009 0001 大理市 0009 1
0014 0008 成都市 0014 1
0005 0002 五华区 0005 2
0007 0002 水富县 0007 2
0006 0005 西园路192号 0006 3
0015 0007 两碗乡 0015 3
0010 0006 金色梧桐 0010 4
0013 0015 两碗村 0013 4
0011 0010 科技有限公司 0011 5
0012 0013 某跨国集团董事长 0012 5
*/
  
  --深度排序（模拟单编码法)
   declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ebh is null
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
 end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh
/*(无列名) ybh ebh beizhu ybh ebh level
----云南省 0001 NULL 云南省 0001 0001 0
  ----昆明市 0002 0001 昆明市 0002 00010002 1
    ----五华区 0005 0002 五华区 0005 000100020005 2
      ----西园路192号 0006 0005 西园路192号 0006 0001000200050006 3
        ----金色梧桐 0010 0006 金色梧桐 0010 00010002000500060010 4
          ----科技有限公司 0011 0010 科技有限公司 0011 000100020005000600100011 5
    ----水富县 0007 0002 水富县 0007 000100020007 2
      ----两碗乡 0015 0007 两碗乡 0015 0001000200070015 3
        ----两碗村 0013 0015 两碗村 0013 00010002000700150013 4
          ----某跨国集团董事长 0012 0013 某跨国集团董事长 0012 000100020007001500130012 5
  ----昭通市 0003 0001 昭通市 0003 00010003 1
  ----大理市 0009 0001 大理市 0009 00010009 1
----贵州省 0004 NULL 贵州省 0004 0004 0
----四川省 0008 NULL 四川省 0008 0008 0
  ----成都市 0014 0008 成都市 0014 00080014 1
  */
  
  
  
  --查找子节点（包括本身节点和子节点)
 declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ybh='0005'
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
 end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh
  
 /*
 (无列名) ybh ebh beizhu ybh ebh level
----五华区 0005 0002 五华区 0005 0005 0
  ----西园路192号 0006 0005 西园路192号 0006 00050006 1
    ----金色梧桐 0010 0006 金色梧桐 0010 000500060010 2
      ----科技有限公司 0011 0010 科技有限公司 0011 0005000600100011 3
      */
  
  --查的父节点（包括本身节点和所有的你节点）
 declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ebh,@level from tb where ebh='0005'
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ebh,b.ebh+a.ebh,@level
            from tb a,@level_tt b
            where a.ybh=b.ybh and b.level=@level-1
 end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh desc
 
 /*
 (无列名) ybh ebh beizhu ybh ebh level
      ----云南省 0001 NULL 云南省 0001 0005000500020001 3
    ----昆明市 0002 0001 昆明市 0002 000500050002 2
  ----五华区 0005 0002 五华区 0005 00050005 1
----西园路192号 0006 0005 西园路192号 0006 0005 0
*/
  

Answer 17

这个很有用。
我经过改进，用了三个函数，目前已在毫秒内。。非常感谢soft_wsx提供思路不过没分给你了。。