使用 SQL Server hierarchyid 列出菜单层次结构 [英] List menu hierarchy using SQL Server hierarchyid

问题描述

我尝试使用数据类型 hierarchyid 构建菜单.

I try to build a menu using the datatype hierarchyid.

我有根节点和当前选择的节点.现在我想列出与湿根和选定节点以及兄弟姐妹相关的所有元素.

I have the root node and the current selected node. now I want to list of all elements that are related wetween root and selected node AND there siblings.

我使用以下 sql 查询获取所有相关元素

I get all related elements with following sql query

DECLARE @rootNode hierarchyid, @selectedNode hierarchyid

SELECT @rootNode = MenuNode FROM CMS_Menu WHERE MenuItemID = 3;

SELECT @selectedNode = MenuNode FROM CMS_Menu WHERE MenuItemID =15;

SELECT CMS_Menu.MenuNode  
FROM CMS_Menu
WHERE @selectedNode.IsDescendantOf(MenuNode) = 1 /*all related elements*/
AND MenuNode.GetLevel() >= @rootNode.GetLevel() /*nothing below root*/

现在我必须为上面查询中的每一行做类似 MenuNode.GetAncestor(1) = result 的事情.

Now I have to do something like MenuNode.GetAncestor(1) = result for each row in the query above.

有谁知道如何在 sql 查询中得到这个?

Does anyone have an idea how to get this in a sql query?

谢谢:)

推荐答案

我不完全确定我理解这个问题，但你能不能用 WHERE 子句做如下类似的事情:

Not entirely sure I understand the question but could you not do something like the following with the WHERE clause:

WHERE @selectedNode.IsDescendantOf(MenuNode.GetAncestor(1)) = 1

汤姆

这篇关于使用 SQL Server hierarchyid 列出菜单层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！