sql server下如何实现按递归查询的结果进行树形结构排序。在线等...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
(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
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
order by level
aa
aaa
aaaa
bb
bbb
bbbb试试:with cte as
(select department_id,
department_name,
isnull(parent_id, -1) parent_id,
1 level,
cast(right('00000'+cast(department_id as varchar),5) as varchar(max)) as sort
from department
where isnull(parent_id, -1) = -1
union all
select t.department_id,
t.department_name,
t.parent_id,
c.level + 1,
cast(sort+right('00000'+cast(t.department_id as varchar),5) as varchar(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 sort