Join
自引用表上的內部聯接直到為空
我有以下 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