我有一个目录树,它根据目录名(实际上是完整的目录路径-“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...)
但没能成功。
1条答案
按热度按时间yuvru6vn1#
您的路径是正确的,行号是()
例子
退货