Sql递归查询创建唯一列表

发布于 2024-09-08 00:58:25 字数 1353 浏览 1 评论 0原文

出于速度原因，我需要将一些代码从 C# 移动到存储过程中。我想要获取的是基于 CategoryId 的 RoleTemplates（或 CategoryToRoleTemplate）表中的 TemplateId 的唯一列表。
但是，我需要查询来遍历 Category.ParentId 关系，并收集所有父级的相关 TemplateId。这需要发生直到 ParentId 为 null。

理想情况下，结果应该是 RoleTemplate.TemplateIds 的唯一列表。

表结构...

Categories
------------------------------
CategoryId    uniqueidentifier
ParentId      uniqueidentifier <-- Relationship to Categories.CategoryId.
Name          varchar (50)

CategoryToRoleTemplate
------------------------------
CategoryId    uniqueidentifier <-- Relationship to Categories.CategoryId.
TemplateId    uniqueidentifier <-- Relationship to RoleTemplates.TemplateId.

RoleTemplates
------------------------------
TemplateId    uniqueidentifier
Name          varchar (50)

我正在使用 SQL Server 2008 R2。

谢谢！

编辑：

最终解决方案：

with CategoryHierarchy (ParentId)
as (
    -- Anchor member definition
    select CategoryId from Categories
    where CategoryId = @id
    union all

    -- Recursive member definition
    (select c.ParentId from Categories as c
        inner join CategoryHierarchy as p
        on c.CategoryId = p.ParentId)
)

select distinct TemplateId from CategoryToRoleTemplates where CategoryId in (select CategoryId from CategoryHierarchy);

感谢所有回答的人！ CTE 是关键。

原文

I need to move some code from C# into a Stored Procedure for speed reasons. What I'm trying to get is a unique list of TemplateIds from the RoleTemplates (or CategoryToRoleTemplate) table based on a CategoryId.
However, I need the query to walk the Category.ParentId relationship, and collection all of the parent's related TemplateIds. This needs to happen until the ParentId is null.

Ideally the result should be a unique list of RoleTemplate.TemplateIds.

Table structure...

Categories
------------------------------
CategoryId    uniqueidentifier
ParentId      uniqueidentifier <-- Relationship to Categories.CategoryId.
Name          varchar (50)

CategoryToRoleTemplate
------------------------------
CategoryId    uniqueidentifier <-- Relationship to Categories.CategoryId.
TemplateId    uniqueidentifier <-- Relationship to RoleTemplates.TemplateId.

RoleTemplates
------------------------------
TemplateId    uniqueidentifier
Name          varchar (50)

I'm using SQL Server 2008 R2.

Thanks!

EDIT:

Final solution:

with CategoryHierarchy (ParentId)
as (
    -- Anchor member definition
    select CategoryId from Categories
    where CategoryId = @id
    union all

    -- Recursive member definition
    (select c.ParentId from Categories as c
        inner join CategoryHierarchy as p
        on c.CategoryId = p.ParentId)
)

select distinct TemplateId from CategoryToRoleTemplates where CategoryId in (select CategoryId from CategoryHierarchy);

Thanks to all who answered! CTEs were the key.

分享到QQ

分享到微博

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

发布评论

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

月野兔 2024-09-15 00:58:25

我建议使用 CTE 来执行该查询。请记住，树实际上将从零开始，直到耗尽为止。

示例（给定您的代码，OOB 可能会也可能不会工作）：

; WITH CategoryTree(CategoryID, sorthelp) AS
(SELECT CategoryID, 0 FROM Categories WHERE ParentID IS NULL)

UNION ALL

(SELECT C.CategoryID, CT.sorthelp + 1 FROM Categories C INNER JOIN CategoryTree CT ON C.PARENTID = CT.CategoryID)

SELECT DISTINCT TemplateID FROM RoleTemplates WHERE CategoryID IN (SELECT CategoryID FROM CategoryTree)

好点（tm）：不要忘记WITH 关键字之前的分号。

I would suggest a CTE for doing that query. Keep in mind though that the tree will actually START at null and go until exhausted.

Example (may or may not work OOB given your code):

; WITH CategoryTree(CategoryID, sorthelp) AS
(SELECT CategoryID, 0 FROM Categories WHERE ParentID IS NULL)

UNION ALL

(SELECT C.CategoryID, CT.sorthelp + 1 FROM Categories C INNER JOIN CategoryTree CT ON C.PARENTID = CT.CategoryID)

SELECT DISTINCT TemplateID FROM RoleTemplates WHERE CategoryID IN (SELECT CategoryID FROM CategoryTree)

Good Point(tm): Don't forget the semicolon before the WITH keyword.

回复收藏 0 原文