小弟求教一个递归的存储过程或者说是sql语句

Question

表结构如下News_TypeId int 主键自增长
News_TypeFatherId int      父节点id
News_StrFatherId nvarchar(200) 记录所有父节点id用逗号隔开现在的记录如下：
News_TypeId  News_TypeFatherId News_StrFatherId
1                 0
2                 1
3                 2
4                 2
5                 3
想要的结果
News_TypeId  News_TypeFatherId News_StrFatherId
1                 0            ,0,1,
2                 1            ,0,1,2,
3                 2            ,0,1,2,3,
4                 2            ,0,1,2,4,
5                 0            ,0,5,
6                 6            ,0,5,6,
7                 5            ,0,5,7,
就是以逗号开始和结束，存的是上一级父节点加上自身的节点id

Answer 1

-------------------------------------------
--  Author : liangCK 小梁
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-05 17:15:50
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (News_TypeId INT,News_TypeFatherId INT)
INSERT INTO @T
SELECT 1,0 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,2 UNION ALL
SELECT 5,3--SQL查询如下:;WITH Liang AS
(
    SELECT *,PATH = CAST(',' + RTRIM(News_TypeFatherid)
                             + ',' + RTRIM(News_TypeId) + ',' AS varchar(MAX))
    FROM @T WHERE news_TypeFatherId = 0
    UNION ALL
    SELECT A.*,CAST(B.PATH + RTRIM(A.News_TypeId) + ',' AS varchar(MAX))
    FROM @T AS A
        JOIN Liang AS B
    ON A.news_TypeFatherId = B.News_TypeId
)
SELECT * FROM Liang/*
News_TypeId News_TypeFatherId PATH
----------- ----------------- ------------------------------------------------------
1           0                 ,0,1,
2           1                 ,0,1,2,
3           2                 ,0,1,2,3,
4           2                 ,0,1,2,4,
5           3                 ,0,1,2,3,5,(5 row(s) affected)*/

Answer 2

上面这位兄弟好像不对劲啊！
生成到一半的时候就不行了啊
这是语句
SELECT *,News_StrFatherId = CAST(',' + RTRIM(News_TypeFatherid)
                             + ',' + RTRIM(News_TypeId) + ',' AS varchar(MAX))
    FROM Web_News_Type WHERE news_TypeFatherId = 0
    UNION ALL
    SELECT A.*,CAST(B.News_StrFatherId + RTRIM(A.News_TypeId) + ',' AS varchar(MAX))
    FROM Web_News_Type AS A
        JOIN Web_News_Type AS B
    ON A.news_TypeFatherId = B.News_TypeId
这是结果

Answer 3

if object_id('tb')is not null drop table tb
go
create table tb(News_TypeId int identity(1,1),News_TypeFatherId int, xh nvarchar(200)) 
insert tb select
0      ,null union all select
1     ,null  union all select
2    ,null   union all select
2     ,null  union all select
3     ,null select * from tbif object_id('f_str')is not null drop function f_str
go
create function dbo.f_str(@id int)
returns varchar(30) 
as
begin
declare @str varchar(30)
set @str=','+ltrim(@id)+','

while exists(select 1 from tb where News_TypeId=@id)
begin
select @str=','+ltrim(News_TypeFatherId)+@str from tb where News_TypeId=@id
set @id=(select News_TypeFatherId from tb where News_TypeId=@id) end

return @str
end
goselect News_TypeId,News_TypeFatherId, News_StrFatherId=dbo.f_str(News_TypeId)
from tb
   
News_TypeId News_TypeFatherId News_StrFatherId
----------- ----------------- ------------------------------
1           0                 ,0,1,
2           1                 ,0,1,2,
3           2                 ,0,1,2,3,
4           2                 ,0,1,2,4,
5           3                 ,0,1,2,3,5,(5 行受影响)