计算逻辑:
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
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
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做不到,需要用到临时表处理吧