日期:2014-05-17 浏览次数:20735 次
with cte as
(select department_id,
department_name,
isnull(parent_id, -1) parent_id,
1 level
from department
where isnull(parent_id, -1) = -1
union all
select t.department_id,
t.department_name,
t.parent_id,
c.level + 1
from department t
join cte c
on t.parent_id = c.department_id)
select department_id, department_name, parent_id, level
from cte
--試試以下:
with cte as
(select department_id,
department_name,
isnull(parent_id, -1) parent_id,
1 LEVEL,
cast(ROW_NUMBER() OVER(order by department_id) as varbinary(MAX)) as sortpath
from department
where isnull(parent_id, -1) = -1
union all
select t.department_id,
t.department_name,
t.parent_id,
c.level + 1,
c.sortpath+cast(ROW_NUMBER() OVER(PARTITION BY t.parent_id order by t.department_id) as varbinary(MAX))
from department t
join cte c
on t.parent_id = c.department_id)
select department_id, department_name, parent_id, level
from cte
ORDER BY sortpath
with cte as
(select department_id,
department_name,
isnull(parent_id, -1) parent_id,
1 level
from department
wh