sql 查询所有下级用户 不解代码
0
[待解决问题]
代码如下：
create table tb(id int,name varchar(10),pid int,px int)
insert into tb values(0,'栏目分类',0,1)
insert into tb values(1,'动物',0,1)
insert into tb values(2,'视频',0,2)
insert into tb values(3,'老虎',1,1)
insert into tb values(4,'狮子',1,2)
insert into tb values(5,'搞笑',2,1)
go
create function dbo.GetTree(@ID int)
returns @t_Level table(ID int,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.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
其中 while 条件下的不懂 （ 最不解 b.Level=@Level-1 ）
所有回答(3)
0
这就好比上一个与下一个对比
0
通过ID找到parentID,在找到其下的子节点.
WHILE @@ROWCOUNT>0 的条件是当INSERT @t_Level SELECT @ID,@Level 有值时会返回受影响的行数.
返回0时就代表结束.
通过CTE会能更好的实现.
试试如下的代码,做了一些改动:
declare @tb table (id int,name Nvarchar(10),pid int,px int)
insert into @tb values(0,N'栏目分类',-1,1)
insert into @tb values(1,N'动物',0,1)
insert into @tb values(2,N'视频',0,2)
insert into @tb values(3,N'老虎',1,1)
insert into @tb values(4,N'狮子',1,2)
insert into @tb values(5,N'搞笑',2,1)
--select * from @tb where id=0
;WITH cte_ct(ID,name,pid,lv)
as
(
select ID,name,pid,0 as lv from @tb where id=0
union all
select b.ID,b.name,b.pid,t.lv+1 as lv from @tb b join cte_ct t on b.pid=t.ID
)
select * from cte_ct
通过CTE会能更好的实现.
？？？？？怎样写？？
试试如下的Code,做了一下改动.
declare @tb table (id int,name Nvarchar(10),pid int,px int)
insert into @tb values(0,N'栏目分类',-1,1)
insert into @tb values(1,N'动物',0,1)
insert into @tb values(2,N'视频',0,2)
insert into @tb values(3,N'老虎',1,1)
insert into @tb values(4,N'狮子',1,2)
insert into @tb values(5,N'搞笑',2,1)
--select * from @tb where id=0
;WITH cte_ct(ID,name,pid,lv)
as
(
select ID,name,pid,0 as lv from @tb where id=0
union all
select b.ID,b.name,b.pid,t.lv+1 as lv from @tb b join cte_ct t on b.pid=t.ID
)
select * from cte_ct
0
@@ROWCOUNT是全局变量，受影响的行数
你上面的insert如果成功，@@ROWCOUNT肯定>0所以才会下面的
