SQL层次结构ID按级别排序

问题描述

是否可以通过层次结构ID对层次结构中的sql数据进行排序，然后对每个级别按字母顺序排序?

Is it possible to sort sql data in a hierarchy by it's hierarchy id, and then for each level sort it say alphabetically?

所以说我们有一个雇员表，该表根据雇员ID列出了组织层次结构

So say we have an Employees Table that lists the organizational hierarchy based on the Employees ID

您有鲍勃(5)，其中有菲尔(17)和查理(28)向他报告，乔西(6)，有泰勒(15)和迈克(56)，向她报告.

You have Bob (5) who has Phil (17) and Charlie(28) Reporting to him, and Josie (6) has Tyler (15) and Mike (56) Reporting to her.

如果您通过HierarchyID对其进行排序，则它将类似于:

If you sort it by HierarchyID it will look like:

鲍勃(/5/)
-菲尔(/5/17/)
-查理(/5/28/)
乔西(/6/)
-泰勒(/6/15/)
-迈克(/6/56/)

Bob (/5/)
--Phil (/5/17/)
--Charlie (/5/28/)
Josie (/6/)
--Tyler (/6/15/)
--Mike (/6/56/)

但是看起来像这样可能更有意义

But It would probably make more sense to have it look like

鲍勃
-查理
-菲尔
乔西
-麦克
-泰勒

Bob
--Charlie
--Phil
Josie
--Mike
--Tyler

有没有可能使它变得太复杂?

Is this possible without it getting too convoluted?

推荐答案

这是我找到的解决方案

declare @oldId hierarchyid, @newId hierarchyid, @parent hierarchyid

select @oldId = id_node, @parent = id_node.GetAncestor(1)
from gbs.T_Hierarchy_Activities ha
where ID = @ID_Object

select @newId = @oldId.GetReparentedValue(@oldId, ID_Node) from (
    select row_number() over(order by id_node) rn, id_node
    from gbs.T_Hierarchy_Activities ha
        cross join (select * from common.FX_Permissions() where ID_Feature = 10) p
    where ID_Node.IsDescendantOf(@oldId.GetAncestor(1)) = 1
        and ID_Level = @oldId.GetLevel()
        and ha.ID_Office = p.ID_Office
) a
where rn = @NewPosition

update gbs.T_Hierarchy_Activities
set ID_Node = case when ID_Node = @oldId then @newId else @oldId end
where ID_Node in (@oldId, @newId)

这篇关于SQL层次结构ID按级别排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！