遞歸父子位置關係記憶體為視圖

這是我的資料結構：

CREATE TABLE Locations(
   LocID int IDENTITY(1,1) NOT NULL,
   LocationTypeID int NOT NULL,
   ParentID int NULL,
   LocNum varchar(50) NULL,
   LocName varchar(250) NULL,
)

CREATE TABLE LocationTypes(
   LocationTypeID int IDENTITY(1,1) NOT NULL,
   TypeName varchar(50) NOT NULL
)

LocationTypes 表包含以下值：

+----------------+----------+
| LocationTypeID | TypeName |
+----------------+----------+
|              1 | Campus   |
|              2 | Building |
|              3 | Area     |
|              4 | Floor    |
|              5 | Room     |
+----------------+----------+

該Locations表包含一堆位置，其中一些ParentID為空（校園），而另一些則為ParentID. 我的資料結構有 5 個層次結構，如LocationTypes表中所示。Campus (1) 是最不具體的，Room(5) 是最具體的。在我的數據庫中，我有其他引用 的表LocID，這些表通常使用 a LocationTypeIDof引用 Locations 5，但並非總是如此。

我需要獲取層次結構中每個位置的名稱以顯示給最終使用者。

最終我想要得到的是具有以下結構的視圖（或記憶體表）：

+-------+---------------+-----------------+-------------+--------------+-------------+
| LocID | CampusLocName | BuildingLocName | AreaLocName | FloorLocName | RoomLocName |
+-------+---------------+-----------------+-------------+--------------+-------------+

我意識到其中一些列最終將為空，對於具有LocationTypeID除 之外的任何位置的位置5，這非常好。

我知道我可能需要一個遞歸公用表表達式，但我真的不知道從哪裡開始。

基本上，此的虛擬碼邏輯是：

• ForeachLocation在LocationsTable
• 得到Parent Location每個的Location，遞歸地繼續，直到沒有更多的父母
• 將此記憶體數據儲存在視圖中，以便可以輕鬆地LEFT JOIN在任何其他引用LocID

我希望一切都說得通。感謝任何可以幫助我的人。

我相信您可以使用此查詢獲得所需的結果：

;WITH CTE_Locations (LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName)
AS
(
   SELECT LocID, 
       LocName AS CampusLocName, 
       CONVERT(varchar(250), NULL) AS BuildingLocName, 
       CONVERT(varchar(250), NULL) AS AreaLocName, 
       CONVERT(varchar(250), NULL) AS FloorLocName, 
       CONVERT(varchar(250), NULL) AS RoomLocName
   FROM Locations
   WHERE ParentID IS NULL

   UNION ALL

   SELECT L.LocID, 
       CampusLocName, 
       IIF(L.LocationTypeID = 2, LocName, BuildingLocName) AS BuildingLocName, 
       IIF(L.LocationTypeID = 3, LocName, AreaLocName) AS AreaLocName, 
       IIF(L.LocationTypeID = 4, LocName, FloorLocName) AS FloorLocName, 
       IIF(L.LocationTypeID = 5, LocName, RoomLocName) AS RoomLocName
   FROM Locations L
       INNER JOIN CTE_Locations CTE ON L.ParentID = CTE.LocID 
)
SELECT LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName
FROM CTE_Locations;

根據WITH common_table_expression文件：

該子句也可以在 CREATE VIEW 語句中用作其定義的 SELECT 語句的一部分。

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