sql - 如何使用排序顺序列对层次结构进行排序

Question

我在 SQL 中有一个包含一些虚拟数据的层次结构：

HierarchyID ParentItemID SortOrder Name
1           NULL         99        Main Parent
2           1            80        Sub Parent 1
3           1            99        Sub Parent 2
4           2            99        Sub Parent 1 child 1
5           3            1         Sub Parent 2 child 1
6           2            89        Sub Parent 1 child 2

我怎样才能排序这个排序顺序驱动排序而不影响层次结构的逻辑？

例如，如果上面只是按 SortOrder 排序，那么层次结构本身就没有意义。Sub Parent 1 child 2 将出现在 Main Parent 上方，这是不允许的。

我们将其作为视觉呈现给用户。必须允许该用户对层次结构出现的顺序进行排序，而不会影响层次结构的逻辑。

我创建了以下构建树视图的 CTE：

WITH EntitiesCTE(ReportingHierarchyTypeID, ReportingHierarchyTypeCode, ReportingHierarchyItemID, Parent, Level, UltimateParent, HasChildren, Treepath, IsActive,ReportingHierarchySortOrder) AS
    ( SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id,
                RHI.ParentItemID, 
                0 AS Level,
                RHI.ReportingHierarchyItemID as UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(RHI.ItemCode AS VARCHAR(1024)) AS Treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      WHERE RHI.ParentItemID is null



      UNION ALL



        SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id, 
                RHI.ParentItemID,
                EntitiesCTE.Level + 1 AS Level,
                EntitiesCTE.UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(EntitiesCTE.treepath + ' -> ' + CAST(RHI.ItemCode AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      INNER JOIN EntitiesCTE
            ON EntitiesCTE.ReportingHierarchyItemID = RHI.ParentItemID
            )



SELECT
    a.*
    ,b.ReportingHierarchyTypeName
    ,e.ItemCode
    ,e.ItemName
    ,e.ParentItemID
    ,e.CompanyID
--    ,e.ReportingHierarchySortOrder




    FROM EntitiesCTE a



INNER JOIN 
    MASTER.ReportingHierarchyType b
    ON  b.ReportingHierarchyTypeID = a.ReportingHierarchyTypeID
INNER JOIN MASTER.ReportingHierarchyItem e
    ON e.ReportingHierarchyItemID = a.ReportingHierarchyItemID
    WHERE a.ReportingHierarchyTypeID = e.ReportingHierarchyTypeID 
    --ORDER BY a.Treepath,a.ReportingHierarchySortOrder, Coalesce(a.parent,0)
    ORDER BY a.Treepath, a.ReportingHierarchySortOrder ASC

但是我得到以下信息：

HierarchyID ParentItemID SortOrder Name
1           NULL         99        Main Parent
2           1            80        Sub Parent 1
4           2            99        Sub Parent 1 child 1
6           2            89        Sub Parent 1 child 2
3           1            99        Sub Parent 2
5           3            1         Sub Parent 2 child 1

要做到这一点，应该交换 HierarchyID 4 和 6。

标签： sqlsql-servertsqlsortinghierarchical-data

Answer 1

我将 Treeview 调整为以下内容并且它有效：

WITH EntitiesCTE(ReportingHierarchyTypeID, ReportingHierarchyTypeCode, ReportingHierarchyItemID, Parent, Level, UltimateParent, HasChildren, Treepath, IsActive,ReportingHierarchySortOrder, ReportingHierarchySortOrderPath) AS
    ( SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id,
                RHI.ParentItemID, 
                0 AS Level,
                RHI.ReportingHierarchyItemID as UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(RHI.ItemCode AS VARCHAR(1024)) AS Treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder,
                CAST(RHI.ReportingHierarchySortOrder AS VARCHAR(1024)) AS ReportingHierarchySortOrderPath
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      WHERE RHI.ParentItemID is null



      UNION ALL 



        SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id, 
                RHI.ParentItemID,
                EntitiesCTE.Level + 1 AS Level,
                EntitiesCTE.UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(EntitiesCTE.treepath + ' -> ' + CAST(RHI.ItemCode AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder,
                CAST(EntitiesCTE.ReportingHierarchySortOrderPath + ' -> ' + CAST(RHI.ReportingHierarchySortOrder AS VARCHAR(1024)) AS VARCHAR(1024)) AS ReportingHierarchySortOrderPath
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      INNER JOIN EntitiesCTE
            ON EntitiesCTE.ReportingHierarchyItemID = RHI.ParentItemID
            )



SELECT
    a.*
    ,b.ReportingHierarchyTypeName
    ,e.ItemCode
    ,e.ItemName
    ,e.ParentItemID
    ,e.CompanyID
--    ,e.ReportingHierarchySortOrder




    FROM EntitiesCTE a



INNER JOIN 
    MASTER.ReportingHierarchyType b
    ON  b.ReportingHierarchyTypeID = a.ReportingHierarchyTypeID
INNER JOIN MASTER.ReportingHierarchyItem e
    ON e.ReportingHierarchyItemID = a.ReportingHierarchyItemID
    WHERE a.ReportingHierarchyTypeID = e.ReportingHierarchyTypeID 
    ORDER BY a.ReportingHierarchySortOrderPath ASC