求一个sql递归函数.生成"树枝"

[fengyunxnc]

/*
表:MES_WuLiaoData
字段:wuliaoid,WuLiaoName,ShangCengWuLiao
要求传进一个wuliaoid可以找出以一个小树枝出来.
如:
wuliaoid,WuLiaoName,ShangCengWuLiao
1,a,0 
2,b,1 
3,c,2 
4,d,3 
5,e,2 
6,e1,5 
7,e2,6 
8,e3,7
当传进2去时要出
2,b,1
3,c,2
5,e,2 
6,e1,5 
7,e2,6 
8,e3,7

*/
--自己写了一个但是不好用,请高手指点
create function Tree_rs(@wuliaoid bigint)
returns @Tree_rs table(wuliaoid bigint,WuLiaoName varchar(100))
begin
declare @ShangCengWuLiao bigint, @WuLiaoName varchar(100)
DECLARE wuliao_tree CURSOR FOR
select wuliaoid,WuLiaoName,ShangCengWuLiao from MES_WuLiaoData where ShangCengWuLiao=@wuliaoid order by WuLiaoID

OPEN wuliao_tree
while @@FETCH_STATUS = 0
begin
FETCH NEXT FROM wuliao_tree into    
    @wuliaoid,@WuLiaoName,@ShangCengWuLiao

    Insert @Tree_rs
    values(@wuliaoid,@WuLiaoName)
        --此处不知道,写什么
    
end


CLOSE wuliao_tree

return
end

[fengyunxnc]

这是第三次发贴..

[wzy_love_sly]

create table tb(wuliaoid int,WuLiaoName varchar(20),ShangCengWuLiao int)
insert into tb select 1,'a',0
insert into tb select 2,'b',1
insert into tb select 3,'c',2
insert into tb select 4,'d',3
insert into tb select 5,'e',2

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 a.wuliaoid,@Level
        FROM tb a,@t_Level b
        WHERE a.ShangCengWuLiao=b.ID
            AND b.Level=@Level-1
    END
    RETURN
END

select a.* from tb a join dbo.f_Cid(2) b on a.wuliaoid=b.id
where level=1 or level=2

还发干什么？

[flairsky]

看上去会比较慢……

mark，学习

[liangCK]

又来?

[sbqcel]

要是是SQL 2005的话就简单了

[fengyunxnc]

我没有学会..
我求求你们了..给我说说怎么搞吧..

[fengyunxnc]

世界因为有你而精彩~ぐ~（紫煈）
不好用..我试过了...

[wzy_love_sly]

你得说你要啥结果啊！
前面你说要1级和2级
到最后好象说的所有....

[ojuju10]

--05新语法


create table #t1(wuliaoid int,wuliaoname varchar(10),shangcengwuliao int)
insert into #t1 select 1,'a',0
insert into #t1 select 2,'b',1
insert into #t1 select 3,'c',2
insert into #t1 select 4,'d',3
insert into #t1 select 5,'e',2
insert into #t1 select 6,'e1',5
insert into #t1 select 7,'e2',6
insert into #t1 select 8,'e3',7


with cet as
(select * from #t1
 where wuliaoid=2
 union all
 select a.* from #t1 a, cet b
where a.shangcengwuliao=b.wuliaoid
)
select * from cet

wuliaoid    wuliaoname shangcengwuliao
----------- ---------- ---------------
2           b          1
3           c          2
5           e          2
6           e1         5
7           e2         6
8           e3         7
4           d          3

(7 行受影响)

[wzy_love_sly]

怎么不好用？

[-狙击手-]

表:MES_WuLiaoData
字段:wuliaoid,WuLiaoName,ShangCengWuLiao
要求传进一个wuliaoid可以找出以一个小树枝出来.
如:
wuliaoid,WuLiaoName,ShangCengWuLiao
1,a,0
2,b,1
3,c,2
4,d,3
5,e,2
6,e1,5
7,e2,6
8,e3,7
当传进2去时要出
2,b,1
3,c,2
5,e,2
6,e1,5
7,e2,6
8,e3,7


0－－－－
为什么没有4,d,3

[fengyunxnc]

longdchuanren
的回答是正确的.可是我的是2000跑不了你写的东西...

[fengyunxnc]

无枪狙击手
应该有:4,d,3
我的失误.

[fengyunxnc]

http://topic.csdn.net/u/20080407/11/6f2fccc1-816a-4431-811c-a924463d04b3.html
http://topic.csdn.net/u/20080407/14/8c53338b-6435-4f5c-a5fe-c304c0644c0b.html?seed=1540106422
http://topic.csdn.net/u/20080407/15/23fff58d-dfcc-47cc-9e63-28af7f609534.html?seed=1163588252

[wzy_love_sly]

create table tb(wuliaoid int,WuLiaoName varchar(20),ShangCengWuLiao int)
insert into tb select 1,'a',0
insert into tb select 2,'b',1
insert into tb select 3,'c',2
insert into tb select 4,'d',3
insert into tb select 5,'e',2
insert into tb select 6,'e1',5
insert into tb select 7,'e2',6
insert into tb select 8,'e3',7

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 a.wuliaoid,@Level
        FROM tb a,@t_Level b
        WHERE a.ShangCengWuLiao=b.ID
            AND b.Level=@Level-1
    END
    RETURN
END

select a.* from tb a 
join dbo.f_Cid(2) b 
on a.wuliaoid=b.id

wuliaoid WuLiaoName ShangCengWuLiao
2 b 1
3 c 2
4 d 3
5 e 2
6 e1 5
7 e2 6
8 e3 7

都听不明白你问什么？别人怎么帮你？

[-狙击手-]

create table ta(wuliaoid int,wuliaoname varchar(10),shangcengwuliao int)
insert into ta select 1,'a',0
insert into ta select 2,'b',1
insert into ta select 3,'c',2
insert into ta select 4,'d',3
insert into ta select 5,'e',2
insert into ta select 6,'e1',5
insert into ta select 7,'e2',6
insert into ta select 8,'e3',7
go


--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int
    set @i = 1
    insert into @t select wuliaoid,shangcengwuliao,@i 
    from ta 
    where wuliaoid = @ID --先找到本身
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.wuliaoid,a.shangcengwuliao,@i 
        from 
            ta a,@t b 
        where 
            a.shangcengwuliao=b.id and b.Level = @i-1
    end
    return
end
go

select a.*
from ta a
right join dbo.f_getChild(2) b
on a.wuliaoid = b.id
drop table ta


drop function f_getChild

/*
wuliaoid    wuliaoname shangcengwuliao 
----------- ---------- --------------- 
2           b          1
3           c          2
5           e          2
4           d          3
6           e1         5
7           e2         6
8           e3         7

（所影响的行数为 7 行）

*/

[fengyunxnc]

OK了..谢谢高手啊..

我有点看不明白..
while @@rowcount<>0
看上去是要跑一个while可是rowcount是指哪个记录集啊?我怎么看不出来?

[wzy_love_sly]

rowcount是指上条语句所影响的行数

[fengyunxnc]

谢谢...以后再多给你分吧..