如何以更简便，更快捷的方式在层次结构表中设置项目的“顺序”？ [英] How to set the “Order” of Items in a hierarchy table in an easier and shorter way?

问题描述

引用此帖子，这里是整体问题和代码：

Refering to this post, here is the overall question and codes:

declare @tbl table (MenuItemID uniqueidentifier, PID uniqueidentifier, MenuID uniqueidentifier, SO tinyint, lvl tinyint)
;WITH 
cte (MenuItemID, PID, MenuID, SO, lvl) AS 
(
    select MenuItemID, PID, MenuID, SO, 0 from MenuItems
        where del = 0 and Perms = 1 and MenuID = @MenuID and MenuID = PID
    UNION ALL
    SELECT MenuItems.MenuItemID, MenuItems.PID, MenuItems.MenuID, MenuItems.SO, cte.lvl + 1 FROM MenuItems 
        JOIN cte ON cte.MenuItemID = MenuItems.PID
    ) 
    select * from cte
        ORDER BY lvl, SO
insert into @tbl select * from cte

declare @tbl2 table (MenuItemID uniqueidentifier, PID uniqueidentifier, MenuID uniqueidentifier, SO tinyint, lvl tinyint)
;with hier (MenuItemID, PID, MenuID, SO, lvl, FullSO) as
(select l0.*, convert(varchar(max),right('000'+convert(varchar(3),SO),3)) FullSO 
 from @tbl l0 where lvl=0
 union all
 select ln.*, lp.FullSO+','+right('000'+convert(varchar(3),ln.SO),3) FullSO
 from @tbl ln
 join hier lp on ln.PID = lp.MenuItemID)

insert into @tbl2 
 select MenuItemID, 
       PID, 
       MenuID,
       rank() over (partition by PID order by SO) SO,
       lvl
from hier
order by FullSO, SO

 update MenuItems set SO = h.SO
    from MenuItems as mi
    join @tbl2 h on mi.MenuItemID = h.MenuItemID and mi.MenuID = h.MenuID

我想知道此代码是否有更简便的方法？

I'd like to know whether there is an easier and shorter way for this code?

谢谢

Kardo

推荐答案

您仍然需要递归CTE来确定层次结构的哪些顶级记录的del = 0和Perms = 1，但以下操作应该更简单：

You will still need a recursive CTE to determine which top-level records for the hierarchy have del = 0 and Perms = 1, but the following should be simpler:

WITH cte AS 
(select MenuItemID, PID, MenuID, SO, 
        rank() over (partition by PID order by SO) newSO
 from MenuItems
 where del = 0 and Perms = 1 and MenuID = @MenuID and MenuID = PID
 UNION ALL
 SELECT m.MenuItemID, m.PID, m.MenuID, m.SO, 
        rank() over (partition by m.PID order by m.SO) newSO
 FROM MenuItems m
 JOIN cte c ON c.MenuItemID = m.PID
) 
update MenuItems set SO = h.newSO
from MenuItems as mi
join cte h on mi.MenuItemID = h.MenuItemID and mi.MenuID = h.MenuID;

SQLFiddle 此处。

SQLFiddle here.

这篇关于如何以更简便，更快捷的方式在层次结构表中设置项目的“顺序”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！