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

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将行号（同级按名称排序）进行了格式化，以方便后期排序