在SQL Server 2005中进行层次查询的排序

Question

我有以下问题：我有一个用于维护分层数据的表。我想使用来自 SQL 2005 的 CTE。

WITH tree (id, parentid, code, name) AS
(
    SELECT id, ofs.ParentID, ofs.code, ofs.name
      FROM OrganizationFeatures ofs
     WHERE ofs.ParentID IS NULL

    UNION ALL

    SELECT ofs.id, ofs.ParentID, ofs.code, ofs.name
      FROM OrganizationFeatures ofs
      JOIN tree ON tree.ID = ofs.ParentID
)

select * from tree

但我想按代码排序，结果应如下所示：

1
1/1
1/1/1
1/1/2
1/2/1
1/2/2
2
4/1

等等，有什么想法吗？

- frantisek

Answer 1

要获取连接后的值，需要在with语句中完成。

要进行排序，需要在最后一个select语句中添加order by。

WITH tree (id, parentid, code, name) AS
(
    SELECT id, ofs.ParentID, ofs.code, ofs.name
      FROM OrganizationFeatures ofs
     WHERE ofs.ParentID IS NULL

    UNION ALL

    SELECT ofs.id, ofs.ParentID, tree.code+'/'+ofs.code, ofs.name
      FROM OrganizationFeatures ofs
      JOIN tree ON tree.ID = ofs.ParentID
)

select * from tree order by code

此外，如果code不是varchar类型，你需要在此代码段中将code列转换为varchar类型，以使代码(tree.code+'/'+ofs.code)正常工作。

- Loki

Answer 2

洛基，我有一个类似的问题，但它没有按照我的要求按名称排序，而是按代码排序 - 当时是星期五，我被压得喘不过气来。

无论如何，运行你的查询给了我一个错误，需要进行转换；我不得不按照以下方式进行更改：

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

但我不喜欢需要强制转换的解决方案。有更好的解决方案吗？

- frantisek