如何使用hierarchyid sql显示所有递归结果

发布于 2024-10-27 15:48:42 字数 1269 浏览 1 评论 0原文

我有一个表 categories：

 ID | NAME                  | PARENT ID    | POSITION   | LEVEL     | ORDER
 ----------------------------------------------------------------------------
 1  | root                  | -1           | 0x         | 0         | 255
 2  | cars                  | 1            | 0x58       | 1         | 10
 5  | trucks                | 1            | 0x68       | 1         | 10
 13 | city cars             | 2            | 0x5AC0     | 2         | 255
 14 | offroad cars          | 2            | 0x5B40     | 2         | 255

其中：

 ID int ident 
 NAME nvarchar(255)
 PARENT ID int
 POSITION hierarchyid
 LEVEL hierarchyid GetLevel()
 ORDER tinyint

此表 model 指定模型名称及其所属类别。示例：

 ID  | NAME      | CATEGORY
 -----------------------------
 1   | Civic     | 13
 2   | Pajero    | 14
 3   | 815       | 5
 4   | Avensis   | 13

其中：

 ID int ident
 NAME nvarchar(255)
 CATEGORY int link to ID category table

我想要做的是能够显示：

所有模型 - 将递归地显示根目录中的所有模型，
类别汽车（包括汽车）中的
模型来自城市汽车（或其孩子（如果有的话）

如何使用hierarchyid进行此类过滤以及如何将结果表与模型连接起来？这是显示特定级别的所有模型结果的快速方法吗？

原文

I have a table categories:

 ID | NAME                  | PARENT ID    | POSITION   | LEVEL     | ORDER
 ----------------------------------------------------------------------------
 1  | root                  | -1           | 0x         | 0         | 255
 2  | cars                  | 1            | 0x58       | 1         | 10
 5  | trucks                | 1            | 0x68       | 1         | 10
 13 | city cars             | 2            | 0x5AC0     | 2         | 255
 14 | offroad cars          | 2            | 0x5B40     | 2         | 255

where:

 ID int ident 
 NAME nvarchar(255)
 PARENT ID int
 POSITION hierarchyid
 LEVEL hierarchyid GetLevel()
 ORDER tinyint

This table model specifies model name and category where it belongs. Example:

 ID  | NAME      | CATEGORY
 -----------------------------
 1   | Civic     | 13
 2   | Pajero    | 14
 3   | 815       | 5
 4   | Avensis   | 13

where:

 ID int ident
 NAME nvarchar(255)
 CATEGORY int link to ID category table

What I am trying to do is to be able to show:

all models - would show all models from root recursively,
models within category cars (cars included)
models from city cars (or its children if any)

How do I use hierarchyid for such filtering and how to join the table for results with models? Is that a quick way how to show all model results from certain level?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

折戟 2024-11-03 15:48:42

我相信这会给您所寻找的内容：

declare @id hierarchyid

select @id = Position from Categories where Name = 'root' -- or 'cars', 'city cars', etc.

select m.* 
from Models m 
    join Categories c on m.Category = c.ID
where c.Position.IsDescendantOf(@id) = 1

有关 IsDescendantOf 方法和其他 hierarchyid 方法的更多信息，请检查方法参考。

I believe this would have given you what you were looking for:

declare @id hierarchyid

select @id = Position from Categories where Name = 'root' -- or 'cars', 'city cars', etc.

select m.* 
from Models m 
    join Categories c on m.Category = c.ID
where c.Position.IsDescendantOf(@id) = 1

For more information on the IsDescendantOf method and other hierarchyid methods, check the method reference.

回复收藏 0 原文