使用CTE向树数据添加排序覆盖

Question

我有一个目录树，它对目录名进行排序(实际上是完整的目录路径-- "root > sub1 > sub2 > my catalog")。我添加了一个"Index“列来覆盖目录中的排序。在查看单个目录的直接后缀时，我能够使它工作，但在查看整个目录树时，我无法让它工作。

dbo.Catalogs
- CatalogID (int,identity,key)
- Name
- ParentCatalogID (int - 0 for root level catalogs)
- Index (int=0, new field to override alpha sort - higher numbers should take priority)

我要获得整个目录树的查询是：

;with CatalogList as
    ( 
        -- top level catalogs
        select Catalogs.CatalogID, [Name], 
            ParentCatalogID, 1 as CatalogLevel, 
            cast([Name] as varchar(max)) as CatalogPath, Catalogs.[Index]
        from Catalogs
        where ParentCatalogID = 0           

        union all

        -- sub catalogs, building CatalogPath & CatalogLevel
        select Catalogs.CatalogID, Catalogs.[Name], 
            Catalogs.ParentCatalogID, CL.CatalogLevel + 1, 
            CL.CatalogPath + ' > ' + Catalogs.[Name] as CatalogPath, Catalogs.[Index]
        from Catalogs
        inner join CatalogList as CL on CL.CatalogID = Catalogs.ParentCatalogID
        where Catalogs.ParentCatalogID > 0
    )
select CatalogList.*
from CatalogList
order by CatalogPath

目前，这只适用于alpha排序。下面的输出需要在1665年之前实现CatalogID 1667，因为指数更高。我试着看ROW_NUMBER() OVER(PARTITION BY...)，但一直没能让它正常工作。

​

​

Answer 1

您使用row_number()在正确的路径上

示例

Declare @YourTable Table ([CatalogID] int,[Name] varchar(50),[ParentCatalogID] int,[Index] int)  
Insert Into @YourTable Values 
 (1661,'Canada',0,0)
,(1663,'All Provinces',1661,0)
,(1665,'AG Install & Leasing',1663,0)
,(1666,'Canada Multi-Use',1663,0)
,(1667,'Construnction & Forestry',1663,1)
,(1668,'Turf',1663,'')
,(1664,'Quebec Only',1661,0)

;with cteP as (
      Select CatalogID
            ,[Name]
            ,ParentCatalogID
            ,CatalogLevel = 1
            ,CatalogPath = convert(varchar(500),[Name])
            ,[Index]
            ,Seq = convert(varchar(500),concat('',10000+row_number() over (partition by ParentCatalogID order by [Index] desc,[Name])))
      From   @YourTable
      Where  ParentCatalogID =0
      Union  All
      Select r.CatalogID
            ,r.[Name]
            ,r.ParentCatalogID
            ,p.CatalogLevel + 1
            ,CatalogPath = convert(varchar(500),concat(p.CatalogPath,' > ',r.[Name]))
            ,r.[Index]
            ,Seq = convert(varchar(500),concat(p.Seq,concat('\',10000+row_number() over (partition by r.ParentCatalogID  order by r.[Index] desc,r.[Name]))))
      From   @YourTable r
      Join   cteP p on r.ParentCatalogID  = p.CatalogID
)
Select *
 From  cteP 
 Order By Seq

返回

​

​