SQL Server 递归找出父/子记录，并实现层级关系码???

[张三疯1]

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

怎么获取子级用户所在层级，怎么实现。

[张三疯1]

补充：最终查结查 如:userID=1 数据 userid name FatherUserID level 1 name1 0 1 2 name1 1 2 3 name1 1 2 4 name1 2 3 5 name1 2 3 6 name1 4 4 7 name1 4 4

[szm341]

;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

[张三疯1]

szm341 你的这个方法我试过，不行。 使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式

[中国风]

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

[szm341]

引用 3 楼 zhangqiang1985 的回复:

szm341 你的这个方法我试过，不行。 使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式

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	               1	2
3	name3	               1	2
4	name4	               2	3
5	name5	               2	3
6	name6	               4	4
7	name7	               4	4
*/

[张三疯1]

多谢，原来这个问题。结贴

[张三疯1]

回：szm341

select UserID,LoginName,FatherUserID,cast(1 as int) as [level] from Users where UserID=1 

cast(1 as int) as [level] 这里必须要定义一临时字段类型 ，我的数据是sql 2005的。不知道你的怎么样成功！不过也谢谢你的热心帮助。呵呵！！

[张三疯1]

是我自己的问题。 SQL没有错。

with temp_users as
(
select UserID,LoginName,FatherUserID,1 as [level] from Users where UserID=1
union all
select  A.UserID,A.LoginName,A.FatherUserID,A.IsValid,B.[level]+1  from Users A,temp_users B where A.FatherUserID=B.UserID
)
select * from temp_users
这条语。我多写一个字段A.IsValid

select  A.UserID,A.LoginName,A.FatherUserID,A.IsValid,B.[level]+1  from Users A,temp_users B where A.FatherUserID=B.UserID