sqlserver实现层级树形查询（第二弹）

根据项目的需要，查询树形的数据并且需要按照主键id字段进行降序排列。

查阅了几篇博客，大多是按照默认的排序方式进行排序，无奈只有自己去写了，本人是比较懒的

直接附上sql的案例：

WITH T AS    
(    
    SELECT *,CAST(A.row_id AS VARBINARY(MAX)) AS px     
    FROM (
    SELECT TOP 100 percent row_number() OVER(ORDER BY b.project_id DESC ) AS row_id,b.* FROM big_project b ORDER BY b.project_id DESC 
    ) AS A   
    WHERE NOT EXISTS(SELECT * FROM big_project WHERE project_id=A.[parent_project_id])  AND A.parent_project_id=0  
    UNION ALL     
    SELECT row_number() OVER(ORDER BY A.project_id DESC ) AS row_id,A.*,CAST(B.px+CAST(A.project_id AS VARBINARY) AS VARBINARY(MAX))      
    FROM big_project AS A    
    JOIN T AS B ON A.[parent_project_id]=B.project_id    
)    
SELECT TOP 100 percent * FROM T ORDER BY px ASC

本案例仅供参考，应用中请随机应变。