洛基,我有一个类似的问题,但它没有按照我的要求按名称排序,而是按代码排序 - 当时是星期五,我被压得喘不过气来。
无论如何,运行你的查询给了我一个错误,需要进行转换;我不得不按照以下方式进行更改:
WITH tree (id, parentid, name, code) AS
(
SELECT id, ofs.ParentID, ofs.name, CAST(ofs.name as varchar(255))
FROM OrganizationFeatures ofs
WHERE ofs.ParentID IS NULL
UNION ALL
SELECT ofs.id, ofs.ParentID, ofs.name, CAST(tree.code+'/'+ofs.name as varchar(255))
FROM OrganizationFeatures ofs
JOIN tree ON tree.ID = ofs.ParentID
)
select * from tree order by code
问题在于,尽管名称是varchar类型,但仍然需要转换为varchar类型。对于大型树,varchar(255)可能不足够。因此,我制作了一个版本,在该版本中上述问题不是那么严重:
WITH tree (id, parentid, name, code) AS
(
SELECT id, ofs.ParentID, ofs.name,
CAST(ROW_NUMBER() OVER (ORDER BY ofs.name ASC) as varchar(255))
FROM OrganizationFeatures ofs
WHERE ofs.ParentID IS NULL
UNION ALL
SELECT ofs.id, ofs.ParentID, ofs.name,
CAST(tree.code +'/' + CAST(ROW_NUMBER() OVER (ORDER BY ofs.name ASC) as varchar(255)) as varchar(255))
FROM OrganizationFeatures ofs
JOIN tree ON tree.ID = ofs.ParentID
)
select * from tree order by code
但我不喜欢需要强制转换的解决方案。有更好的解决方案吗?