取得树形结构并对树按名称排序

来源：互联网发布：矩阵张量积计算公式编辑：程序博客网时间：2024/06/02 14:35

WITH    t AS ( SELECT   ID ,                        department ,                        pid ,                        CONVERT(VARCHAR(1000), RIGHT('000000'                                                     + CONVERT(VARCHAR(20), ROW_NUMBER() OVER ( ORDER  BY department )),                                                     6)) AS PATH ,                        1 AS LEVEL               FROM     dbo.department               WHERE    pid IS NULL               UNION ALL               SELECT   dbo.department.ID ,                        dbo.department.department ,                        dbo.department.pid ,                        CONVERT(VARCHAR(1000), PATH + '-' + RIGHT('000000'                                                              + CONVERT(VARCHAR(20), ROW_NUMBER() OVER ( ORDER  BY dbo.department.department )),                                                              6)) AS PATH ,                        t.level + 1 AS LEVEL               FROM     dbo.department                        INNER JOIN t ON department.pid = t.id             )    SELECT  *    FROM    t    ORDER BY Path

其中PATH将行号（同级按名称排序）进行了格式化，以方便后期排序

0 0