这是我的数据结构:
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 LocationTypeID
of引用 Locations 5
,但并非总是如此。
我需要获取层次结构中每个位置的名称以显示给最终用户。
最终我想要得到的是具有以下结构的视图(或缓存表):
+-------+---------------+-----------------+-------------+--------------+-------------+
| LocID | CampusLocName | BuildingLocName | AreaLocName | FloorLocName | RoomLocName |
+-------+---------------+-----------------+-------------+--------------+-------------+
我意识到其中一些列最终将为空,对于具有LocationTypeID
除 之外的任何位置的位置5
,这非常好。
我知道我可能需要一个递归公用表表达式,但我真的不知道从哪里开始。
基本上,此的伪代码逻辑是:
- Foreach
Location
在LocationsTable
- 得到
Parent Location
每个的Location
,递归地继续,直到没有更多的父母 - 将此缓存数据存储在视图中,以便可以轻松地
LEFT JOIN
在任何其他引用LocID
我希望一切都说得通。感谢任何可以帮助我的人。
我相信您可以使用此查询获得所需的结果:
根据WITH common_table_expression文档: