高手请进，如何用一条sql语句遍历一个树型结构

Question

--树形数据查询示例
--作者: 邹建if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union  all  select 0,'美国'
union  all  select 0,'加拿大'
union  all  select 1,'北京'
union  all  select 1,'上海'
union  all  select 1,'江苏'
union  all  select 6,'苏州'
union  all  select 7,'常熟'
union  all  select 6,'南京'
union  all  select 6,'无锡'
union  all  select 2,'纽约'
union  all  select 2,'旧金山'
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO/*--树形数据处理 级别及排序字段--邹建 2003-12(引用请保留此信息)--*//*--调用示例 --调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b 
where a.[id]=b.[id]
order by b.sid
--*/
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b 
where a.[id]=b.[id]
order by b.sid
go
--删除测试
drop table [tb]
drop function f_id
go/*--结果
中国
----北京
----上海
----江苏
--------苏州
------------常熟
--------南京
--------无锡
美国
----纽约
----旧金山
加拿大--*/

Answer 1

CREATE function f_GetRootId(
@id  int
)
returns int
as
begin
  declare @is_id int
  set @is_id = @id
  while (select fartherID from tab1 where id = @is_id) is not null
  begin
    select @is_id = fartherID from tab1 where id = @is_id
  end
  return(@is_id)
endselect * from tab1 order by dbo.f_GetRootId(isnull(fartherId,Id)),isnull(fartherId,Id)

Answer 2

--生成测试数据
CREATE TABLE TAB1(ID INT,NAME CHAR(1),fatherID INT)INSERT INTO TAB1 SELECT 1 ,'A',NULL
INSERT INTO TAB1 SELECT 2 ,'B',NULL
INSERT INTO TAB1 SELECT 3 ,'C',1 
INSERT INTO TAB1 SELECT 4 ,'D',1
INSERT INTO TAB1 SELECT 5 ,'E',3
INSERT INTO TAB1 SELECT 6 ,'F',3
INSERT INTO TAB1 SELECT 7 ,'G',5
INSERT INTO TAB1 SELECT 8 ,'H',6
INSERT INTO TAB1 SELECT 9 ,'I',4
INSERT INTO TAB1 SELECT 10,'J',4
INSERT INTO TAB1 SELECT 11,'K',10
INSERT INTO TAB1 SELECT 12,'L',11
INSERT INTO TAB1 SELECT 13,'M',6
INSERT INTO TAB1 SELECT 14,'N',10
INSERT INTO TAB1 SELECT 15,'O',2
--创建自定义函数
--获取规定格式的节点数据
CREATE FUNCTION f_getRoot(@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @ret VARCHAR(8000)
  SELECT @ret = '+-'+RTRIM(@ID) FROM TAB1 WHERE ID = @ID
  
  WHILE(@@ROWCOUNT<>0)
    SELECT 
        @ret = '  '+STUFF(@ret,CHARINDEX('-',@ret),1,'--'),@ID = fatherID
    FROM
        TAB1 
    WHERE
        ID = @ID AND fatherID IS NOT NULL
  
  RETURN @ret
END--获取排序数据
CREATE FUNCTION f_getOrder(@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @ret VARCHAR(8000)
  SELECT @ret = RIGHT('00000'+RTRIM(@ID),6) FROM TAB1 WHERE ID = @ID
  
  WHILE(@@ROWCOUNT<>0)
    SELECT 
        @ret = RIGHT('00000'+RTRIM(fatherID),6) + @ret,@ID = fatherID
    FROM
        TAB1 
    WHERE
        ID = @ID AND fatherID IS NOT NULL
  RETURN @ret
END--执行查询
SELECT DBO.f_getroot(ID) FROM TAB1 ORDER BY DBO.f_getorder(ID) ASC

Answer 3

http://blog.csdn.net/xluzhong/articles/340928.aspx

Answer 4

这样的效果谁能做一个
AAAA 
├—AAAA-1 
│ ├—AAAA-1-1 
├—AAAA-2 
BBBB 
├—BBBB-1 
CCCC 
├—CCCC-1 
└—CCCC-2 

Answer 5

一條SQL語句就能實現的，還沒有見到，我也是用函數實現的！
期待下面的高手！

Answer 6

如果你用sql 2005 ,一条语句当然没问题. sql 2005 可以使用递归直接实现http://community.csdn.net/Expert/topic/4163/4163676.xml?temp=.211178

Answer 7

SQL2005有这么历害吗,一定要用下