递归算法

[yubofighting]

计算逻辑：
1、先找出主管列为“无”的员工；
2、将第一步中找出的员工，再找出主管是该员工的下级员工；
3、以此递归遍历整张表

有无好的方法？

原始数据：
员工 主管
1 4
2 1
3 6
4 无
5 1
6 8
7 9
8 9
9 1

排序后的数据
员工 主管
4 无
1 4
2 1
5 1
9 1
7 9
8 9
6 8
3 6

[qq_17482963]

with tb(id,pid)
as
(                             
select 1,	4 union all
select 2,	1 union all
select 3,	6 union all
select 4,	NULL union all
select 5,	1 union all
select 6,	8 union all
select 7,	9 union all
select 8,	9 union all
select 9,	1
),
tb1 as 
(
	select id,pid from tb where pid is null
	union all
	select b.id,b.pid from tb1 a,tb b where a.id=b.pid
)
select * from tb1

[yubofighting]

这个解决方法，无法解决层级排序的问题。。。

引用 1 楼 qq_17482963 的回复:

with tb(id,pid)
as
(                             
select 1,	4 union all
select 2,	1 union all
select 3,	6 union all
select 4,	NULL union all
select 5,	1 union all
select 6,	8 union all
select 7,	9 union all
select 8,	9 union all
select 9,	1
),
tb1 as 
(
	select id,pid from tb where pid is null
	union all
	select b.id,b.pid from tb1 a,tb b where a.id=b.pid
)
select * from tb1

[哋岼線]

with tb(id,pid)
as
(                             
select 1,    4 union all
select 2,    1 union all
select 3,    6 union all
select 4,    NULL union all
select 5,    1 union all
select 6,    8 union all
select 7,    9 union all
select 8,    9 union all
select 9,    1 union all
select 11,    2 
),
tb1(id,pid,[level]) AS(
	
	SELECT id,pid,0 FROM tb WHERE pid IS NULL 
	UNION ALL
	SELECT tb.id,tb.pid,tb1.[level]+1 FROM tb,tb1 WHERE tb.pid = tb1.id
	
)

SELECT * FROM tb1 ORDER BY level

这个最多再加个LEVEL, 如果要像oracle那样，恐怕CTE做不到，需要用到临时表处理吧