分层数据的递归查询

Question

我有数据存储在层次结构中。我需要有一个可以向我显示层次结构的查询。所以，床在牢房里，牢房在地板上，在街区的建筑物里。

我需要做的是根据给定的位置ID显示整个位置的方法。

一个人可能在床上，或在牢房里，或者在地板上......或在建筑物中。我有该位置 ID。我需要做的是显示该人所在位置的描述。

例如，此人在单元格中，在数据库中可能是 ID 350。我需要能够构建他的位置字符串，以显示以下内容：

2号楼1号楼2楼2室

或者，用户位于建筑物 1 的某个位置，因此建筑物 1 的 ID 可能是 3。所以我需要展示 1 号楼 2 座。

两个表。一个保存位置，另一个只是查找以查看类型。

有没有一种有效的方法来查询此类数据？我可能还有另一个要求，就是显示几百人的行踪，所以查询基本上运行了很多次。

我尝试了递归 CTE，但我找到的示例只处理一个级别（我认为？

也许返回表的函数是最好的方法？但问题是 - 根据您开始的"级别"，列可能会更少。

我需要一种方法来处理这个问题。

CREATE TABLE [dbo].[Location]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [ParentID] Int NULL,
 [LocationTypeID] Int NOT NULL,
 [Description] Varchar(100) NOT NULL
)
ON [PRIMARY]
go
-- Add keys for table dbo.Location
ALTER TABLE [dbo].[Location] ADD CONSTRAINT [pk_location] PRIMARY KEY ([ID])
 ON [PRIMARY]
go

CREATE TABLE [LocationType]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Description] Varchar(100) NOT NULL
)
ON [PRIMARY]
go
ALTER TABLE [LocationType] ADD CONSTRAINT [pk_location] PRIMARY KEY ([ID])
 ON [PRIMARY]
go

现在我们将填充一些查找数据：

INSERT INTO LocationType (Description) VALUES ('Bed')
INSERT INTO LocationType (Description) VALUES ('Cell')
INSERT INTO LocationType (Description) VALUES ('Floor')
INSERT INTO LocationType (Description) VALUES ('Building')
INSERT INTO LocationType (Description) VALUES ('Block')

然后创建一些位置数据。

-- Create the root items, which are the blocks. We'll have 2.
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (NULL, 5, 'Block A') -- 1
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (NULL, 5, 'Block B') -- 2
-- Now add 3 buildings per block.
-- Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (1, 4, 'Building 1') -- 3
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (1, 4, 'Building 2') -- 4
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (1, 4, 'Building 3') -- 5

-- Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (2, 4, 'Building 1') -- 6
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (2, 4, 'Building 2') -- 7
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (2, 4, 'Building 3') -- 8
-- Now add two floors per building.
-- Building 1 Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (3, 3, '1st Floor') -- 9
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (3, 3, '2nd Floor') -- 10
-- Building 2 Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (4, 3, '1st Floor') -- 11
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (4, 3, '2nd Floor') -- 12
-- Building 3 Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (5, 3, '1st Floor') -- 13
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (5, 3, '2nd Floor') -- 14


-- Building 1 Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (6, 3, '1st Floor') -- 15
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (6, 3, '2nd Floor') -- 16
-- Building 2 Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (7, 3, '1st Floor') -- 17
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (7, 3, '2nd Floor') -- 18
-- Building 3 Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (8, 3, '1st Floor') -- 19
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (8, 3, '2nd Floor') -- 20

-- 现在，只需一个光标即可填充牢房和床。

DECLARE @ThisID INT
DECLARE @Cntr INT  = 0
DECLARE mycursor CURSOR FOR
SELECT id from Location where LocationTypeID = 3
OPEN mycursor
FETCH NEXT FROM mycursor
INTO @ThisID
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Cntr = 1
    WHILE(@Cntr < 20)
    BEGIN
        INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (@ThisID, 4, 'Cell ' + CAST(@Cntr AS VARCHAR))  
        SET @Cntr = @Cntr + 1
    END
    FETCH NEXT FROM mycursor
    INTO @ThisID
END
CLOSE mycursor
DEALLOCATE mycursor

-- Now add two beds per cell
DECLARE my_cursor CURSOR FOR
SELECT id from Location where LocationTypeID = 4
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @ThisID
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (@ThisID, 5, 'Bed 1')   
    INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (@ThisID, 5, 'Bed 2')   
    FETCH NEXT FROM my_cursor
    INTO @ThisID
END
CLOSE my_cursor
DEALLOCATE my_cursor

Answer 1

下面是一个递归 CTE 解决方案：

WITH rCTE AS(
    SELECT
        l.*, CAST(l.Description AS VARCHAR(MAX)) AS FullLoc
    FROM Location l
    INNER JOIN LocationType  lt ON lt.ID = l.LocationTypeID
    WHERE l.ParentID IS NULL
    UNION ALL
    SELECT
        l.*, CAST(l.Description AS VARCHAR(MAX)) + ', ' + r.FullLoc
    FROM Location l
    INNER JOIN LocationType  lt ON lt.ID = l.LocationTypeID
    INNER JOIN rCTE r
        ON r.ID = l.ParentID
)
SELECT
    ID, FullLoc
FROM rCTE 
WHERE ID IN(21, 111, 190)
ORDER BY ID
OPTION (MAXRECURSION 0)

Answer 2

解决此问题的最简单方法是使用 LEFT JOIN。但是当层次结构级别未知或可能变得非常深时，它变得棘手。如果层次结构级别可以预先确定，例如 6，那么此 SQL 代码将起作用

SELECT
    L0.ID,
    L0.[Description] + 
    ISNULL(', ' + L1.[Description], '')  +
    ISNULL(', ' + L2.[Description], '')  +
    ISNULL(', ' + L3.[Description], '')  +
    ISNULL(', ' + L4.[Description], '')  +
    ISNULL(', ' + L5.[Description], '') FullLocation
FROM Location L0
    LEFT JOIN Location L1 ON L1.ID = L0.[ParentID]
    LEFT JOIN Location L2 ON L2.ID = L1.[ParentID]
    LEFT JOIN Location L3 ON L3.ID = L2.[ParentID]
    LEFT JOIN Location L4 ON L4.ID = L3.[ParentID]
    LEFT JOIN Location L5 ON L5.ID = L4.[ParentID]
WHERE
    L0.ID IN (21, 111, 190)

结果

╔═════╦═════════════════════════════════════════╗
║ ID  ║              FullLocation               ║
╠═════╬═════════════════════════════════════════╣
║  21 ║ Cell 1, 1st Floor, Building 1, Block A  ║
║ 111 ║ Cell 15, 1st Floor, Building 3, Block A ║
║ 190 ║ Cell 18, 1st Floor, Building 2, Block B ║
╚═════╩═════════════════════════════════════════╝