SQL Server 递归找出父/子记录，并实现层级关系码???
CREATE TABLE [Users](
[UserID] [bigint],
[LoginName] [nvarchar](50),
[FatherUserID] [bigint]
)

userid name FatherUserID
1 name1 0
2 name1 1
3 name1 1
4 name1 2
5 name1 2
6 name1 4
7 name1 4

根据UserID 递归查询，父级型子级数，并标用户当前所在层级？


-------------------- 递归SQL语句

with temp_users as
(
select UserID,LoginName,FatherUserID from Users where UserID=1
union all
select A.UserID,A.LoginName,A.FatherUserID from Users A,temp_users B where A.FatherUserID=B.UserID
)
select * from w_users

怎么获取子级用户所在层级，怎么实现。
[解决办法]

;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

/*
UserIDLoginNameFatherUserIDlevle
1name1               01
2name2               12 
 
3name3               12
4name4               23
5name5               23
6name6               44
7name7               44
*/