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

ev7lccsx 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(393)

我有一个目录树，它根据目录名（实际上是完整的目录路径-“root>sub1>sub2>my catalog”）排序。我添加了一个“索引”列来覆盖目录中的排序。我可以让它工作时，查看一个目录的直接死者，但我不能让它工作时，查看整个目录树。

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排序。下面的输出需要catalogid1667早于1665，因为索引更高。我试着看着 ROW_NUMBER() OVER(PARTITION BY...) 但没能成功。

sql sql-server common-table-expression tree

来源：https://stackoverflow.com/questions/62141470/adding-sorting-override-to-tree-data-with-cte

1条答案

按热度按时间

yuvru6vn1#

您的路径是正确的，行号是（）
例子

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

退货

赞(0）回复(0）举报 2021-08-09

我来回答

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

1条答案

相关问题

热门标签

最新问答