select * from users where parentID =1然后再一个个的根据userid查询,我现在就是想弄个什么循环或者枚举,一次性把用户关系都弄出来
你的意思是不是,如果用userid = 1去查询,那么你要查询出所有的子节点是吧
是这样吗:建表语句: --1.建表 create table users( UserID int, --节点id parentID int, --父节点id userName varchar(50) )insert into users select 1,0,'xxx' union all select 20150,1,'xxx' union all select 20151,1,'xxx' union all select 20152,20150,'xxx' union all select 20153,20150,'xxx' union all select 20154,20151,'xxx' union all select 20155,20151,'xxx' go 查询语句,包含了循环:--2.定义表变量 declare @tb table ( UserID int, --节点id parentID int, --父节点id userName varchar(50), level int --层级 ) --3.递归开始 insert into @tb select *,1 as level from users where userid = 1 --4.递归的过程 while @@ROWCOUNT > 0 begin
insert into @tb select t1.userid,t1.parentid,t1.username,level + 1 from @tb t inner join users t1 on t.userid = t1.parentid where not exists(select 1 from @tb t2 where t.level < t2.level) end --5.最后查询 select * from @tb t where not exists(select 1 from users t1 where t1.parentid = t.userid) /* UserID parentID userName level 20152 20150 xxx 3 20153 20150 xxx 3 20154 20151 xxx 3 20155 20151 xxx 3 */
你的意思是不是,如果用userid = 1去查询,那么你要查询出所有的子节点是吧
--1.建表
create table users(
UserID int, --节点id
parentID int, --父节点id
userName varchar(50)
)insert into users
select 1,0,'xxx' union all
select 20150,1,'xxx' union all
select 20151,1,'xxx' union all
select 20152,20150,'xxx' union all
select 20153,20150,'xxx' union all
select 20154,20151,'xxx' union all
select 20155,20151,'xxx'
go
查询语句,包含了循环:--2.定义表变量
declare @tb table
(
UserID int, --节点id
parentID int, --父节点id
userName varchar(50),
level int --层级
)
--3.递归开始
insert into @tb
select *,1 as level
from users
where userid = 1
--4.递归的过程
while @@ROWCOUNT > 0
begin
insert into @tb
select t1.userid,t1.parentid,t1.username,level + 1
from @tb t
inner join users t1
on t.userid = t1.parentid
where not exists(select 1 from @tb t2
where t.level < t2.level)
end
--5.最后查询
select *
from @tb t
where not exists(select 1 from users t1 where t1.parentid = t.userid)
/*
UserID parentID userName level
20152 20150 xxx 3
20153 20150 xxx 3
20154 20151 xxx 3
20155 20151 xxx 3
*/