/*
表: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
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
--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 行受影响)
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
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 行）
*/
