自引用表上的內部聯接直到為空

我有以下 SQL 表：

OrganizationalUnit
   ID - PK
   ParentOrganizationalUnit - FK
   Title
   Department_Code (optional)
   Division_Code (optional)

Person
   ID - PK
   OrganizationalUnitID - FK
   FirstName
   LastName

為了這個例子，這些表如下所示：

OrganizationalUnit:
0 | (null) | Information Technology |  101   | (null)
1 | 0      | Database Management    | (null) | 4000
2 | (null) | Human Resources        |  102   | (null)
3 | 2      | Recruitment            | (null) | 4000
4 | (null) | Finance                | (null) | (null)
5 | 1      | SQL                    | (null) | (null)

People
0 | 0 | John    | Doe
1 | 0 | Jane    | Doe
2 | 1 | Josh    | Doe
3 | 1 | Jack    | Doe
4 | 3 | Other   | Doe
5 | 4 | Finance | Doe
6 | 5 | Odd     | Doe

我需要執行一個查詢，以便獲得以下輸出：

Output
PersonID | OrganizationalUnitID | Department_Code | Division_Code | First Name | last Name
0        |         0            |       101       |       (null)  |  John      |  Doe
1        |         0            |       101       |       (null)  |  Jane      |  Doe
2        |         1            |       101       |       4000    |  Josh      |  Doe
3        |         1            |       101       |       4000    |  Josh      |  Doe
4        |         3            |       102       |       4000    |  Other     |  Doe
5        |         4            |      (null)     |       (null)  |  Finance   |  Doe
6        |         5            |      101        |       4000    |   Odd       |  Doe

department_code 和 division_code 欄位用於將 OrganizationalUnits 與另一個數據庫關聯。這個其他數據庫最多只能跟踪 2 級層次結構（部門和部門），而我被要求跟踪無限層次的層次結構，但將第一級映射到部門，第二級映射到部門。我不知道從哪裡開始編寫可以執行此操作的查詢。我可能不得不重新設計我的 ERD。

SQL Server 中有一個章節叫做：Recursive Queries Using Common Table Expressions。它解釋得很好，並且有一個非常清晰和簡單的例子。

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186243(v=sql.105)

基於this documentation，我的查詢解決方案如下（測試OK）：

WITH CTE_RecursiveOU (ID, ParentID, DepCode,DivCode)
AS
(
-- Anchor member definition
   SELECT 
       u.ID as ID,
       u.ParentOrganizationalUnit as ParentID, 
       u.Department_Code as  DepCode,
       u.Division_Code as DivCode
   FROM dbo.OrganizationalUnit AS u
   WHERE u.ParentOrganizationalUnit IS NULL
   UNION ALL
-- Recursive member definition
   SELECT          
       u.ID as ID,
       u.ParentOrganizationalUnit as ParentID, 
       r.DepCode as  DepCode,
       ISNULL(r.DivCode,u.Division_Code) as DivCode
   FROM dbo.OrganizationalUnit AS u
   INNER JOIN CTE_RecursiveOU AS r
       ON u.ParentOrganizationalUnit = r.ID
)
-- Statement that executes the CTE
SELECT  
   p.ID as PersonID, 
   c.ID as OrganizationalUnitID,
   c.DepCode as Department_Code, 
   c.DivCode as Division_Code,
   p.FirstName,
   p.LastName
FROM CTE_RecursiveOU AS c
INNER JOIN dbo.Person AS p
   ON p.OrganizationalUnitID = c.ID;

GO

引用自：https://dba.stackexchange.com/questions/215088