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

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