递归查询如何进行树形结构排序？

[qiangorqiang]

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

[---涛声依旧---]

精华帖里去搜吧，有很多的

[haitao]

通过字符串累加出所有的上级节点的id（或名称），再按此排序

[lzw_0736]

--試試以下：
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

[Andy__Huang]

http://bbs.csdn.net/topics/390631414

[LongRui888]

关键是你想按照那个来排序，这里按照树的层次，进行排序,那么在显示的时候是，
先显示第一层，然后显示第二层，然后是第三层，……：

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

[LongRui888]

如果是要按照层级化来显示，比如： x --父节点 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

[qiangorqiang]

谢谢各位！！