日期:2014-05-17 浏览次数:20621 次
;with temp_users as
(
select UserID,LoginName,FatherUserID,1 as levle from #t where UserID=1
union all
select A.UserID,A.LoginName,A.FatherUserID,B.levle+1 from #t A,temp_users B where A.FatherUserID=B.UserID
)
select * from temp_users
with temp_users as
(
select UserID,LoginName,FatherUserID,cast(1 as int) as [level] from Users where UserID=1
union all
select A.UserID,A.LoginName,A.FatherUserID,b.[level]+1 as [level] from Users A,temp_users B where A.FatherUserID=B.UserID
)
select * from w_users
select identity(int,1,1)userid,* into #t from(
select 'name1'LoginName,0 FatherUserID
union all select 'name2', 1
union all select 'name3', 1
union all select 'name4', 2
union all select 'name5', 2
union all select 'name6', 4
union all select 'name7', 4
)a
;with temp_users as
(
select UserID,LoginName,FatherUserID,1 as levle from #t where UserID=1
union all
select A.UserID,A.LoginName,A.FatherUserID,B.levle+1 from #t A,temp_users B where A.FatherUserID=B.UserID
)
select * from temp_users
/*
UserID LoginName FatherUserID levle
1 name1 0 1
2 name2