62,025
社区成员
发帖
与我相关
我的任务
分享
1. create function c_tree(@initid int)/*定义函数c_tree,输入参数为初始节点id*/
2. returns @t table(id int,name varchar(100),parentid int,lev int)/*定义表t用来存放取出的数据*/
3. begin
4. declare @i int/*标志递归级别*/
5. set @i=1
6. insert @t select id,name,parentid,@i from tb_city where id=@initid
7. while @@rowcount<>0
8. begin
9. set @i=@i+1
10. insert @t select a.id,a.name,a.parentid,@i from tb_city as a,@t as b
11. where b.id=a.parentid and b.lev=@i-1
12. end
13. return
14. end
create function c_tree(@initid int)/*定义函数c_tree,输入参数为初始节点id*/
returns @t table(id int,name varchar(100),parentid int,lev int)/*定义表t用来存放取出的数据*/
begin
declare @i int/*标志递归级别*/
set @i=1
insert @t select id,name,pid ,@i from tb where id=@initid
while @@rowcount<>0
begin
set @i=@i+1
insert @t select a.id,a.name,a.pid ,@i from tb as a,@t as b
where b.id=a.pid and b.lev=@i-1
end
return
end
DECLARE @Table TABLE(Id int,Pid int,Name char)
INSERT INTO @Table
SELECT 1,0,'a' UNION ALL
SELECT 2,0,'b' UNION ALL
SELECT 3,1,'c' UNION ALL
SELECT 4,3,'d' UNION ALL
SELECT 5,4,'e'
;WITH List AS(
SELECT * FROM @Table T WHERE T.Name='a'
UNION ALL
SELECT T2.* FROM List T , @Table T2 WHERE T.Id=T2.Pid
)
SELECT L.* FROM List L
/*
Id Pid Name
----------- ----------- ----
1 0 a
3 1 c
4 3 d
5 4 e
(4 行受影响)
*/