选择所有层次结构级别及以下 SQL Server [英] Select all hierarchy level and below SQL Server

问题描述

我在这方面遇到了困难.我已经看到了一些关于如何从给定父级的自引用表中获取所有子记录，甚至如何获取子记录的父级的示例.

I am having a difficult time with this one. I have seen a few examples on how to obtain all child records from a self referencing table given a parent and even how to get the parents of child records.

我想要做的是返回一个记录和所有给定 ID 的子记录.

What I am trying to do is return a record and all child records given the ID.

把这个放在上下文中 - 我有一个公司层次结构.哪里:

To put this into context - I have a corporate hierarchy. Where:

#Role        Level#
--------------------
Corporate         0
Region            1
District          2
Rep               3

我需要的是一个程序，它 (1) 确定记录的级别，以及 (2) 检索该记录和所有子记录.

What I need is a procedure that (1) figures out what level the record is and (2) retrieves that record and all children records.

作为一个地区的想法可以看到一个地区的所有地区和代表，地区可以看到他们的代表.销售代表只能看到自己.

The idea being a Region can see all districts and reps in a district, Districts can see their reps. Reps can only see themselves.

我有桌子:

ID            ParentId           Name
-------------------------------------------------------
1             Null               Corporate HQ
2             1                  South Region
3             1                  North Region
4             1                  East Region
5             1                  West Region
6             3                  Chicago District
7             3                  Milwaukee District
8             3                  Minneapolis District
9             6                  Gold Coast Dealer
10            6                  Blue Island Dealer

我该怎么做:

CREATE PROCEDURE GetPositions
    @id int
AS
BEGIN
    --What is the most efficient way to do this--
END
GO

例如@id = 3的预期结果，我想返回:

For example the expected result for @id = 3, I would want to return:

3, 6, 7, 8, 9, 10

如果您对此有任何帮助或想法，我将不胜感激.

I'd appreciate any help or ideas on this.

推荐答案

你可以通过递归 CTE 做到这一点:

You could do this via a recursive CTE:

DECLARE @id INT = 3;

WITH rCTE AS(
    SELECT *, 0 AS Level FROM tbl WHERE Id = @id
    UNION ALL
    SELECT t.*, r.Level + 1 AS Level
    FROM tbl t
    INNER JOIN rCTE r
        ON t.ParentId = r.ID
)
SELECT * FROM rCTE OPTION(MAXRECURSION 0);

在线演示

这篇关于选择所有层次结构级别及以下 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！