sqlserver2005－树结构－排序－查询

sqlsever2005中，可以使用ＣＴＥ实现像oracle中connect by，prior查询树结构的功能，下面就是树形结构＋排序的查询

表结构
kmid(id自增int) kmname(名称varchar) fkmid(父id) kmcode(编码varchar) leaf(节点级别int) sort(排序编号int)

WITH sortCTE
AS
( 
　　SELECT top 100  percent　*, 0 AS lvl,   
    CAST(CAST(sort AS VARCHAR(10))  
         AS VARCHAR(MAX)) AS path   --"sort" 排序编号字段
    FROM dbo.cxfkm　--"cxfkm"查询表
    WHERE leaf=1　--根节点条件,这儿是所有１级节点
　　order by sort asc　--先把１级节点排序

  UNION ALL   

  SELECT C.*, P.lvl + 1,   
    CAST(P.path+'-' + CAST(C.sort AS VARCHAR(10))    
         AS VARCHAR(MAX)) AS path   
  FROM sortCTEAS P   
    JOIN dbo.cxfkm AS C   
      ON C.fkmid = P.kmid   
)   
SELECT * --REPLICATE(' |-    ', lvl) + kmname AS kmname,可以对某些字段修饰
FROM sortCTE
ORDER BY path

这有篇详细介绍递归查询的文章：http://blog.csdn.net/feixianxxx/archive/2009/11/01/4753783.aspx