sql-server - SQL Server - 从叶子到根的递归 CTE(反向)

Question

想象一下以下场景:我有很多层次，从上层(根 parent )到下层( child 或叶子)。

                   (root parent)               LEVEL 0
                       ID:98
                      /     \
                     /       \
                    /         \
                   o           +               LEVEL 1
                ID:99        ID:100
                             /   \
                            /     \
                           o       +           LEVEL 2
                        ID:101   ID:102
                                  / \
                                 /   \ 
                                o     o        LEVEL 3         
                            ID:201  ID:202

现在想象一下“+”符号是房间。同一层的房间之间不能互通。每个房间都有一些门。通过这些门，您可以与另一层的其他房间( child )交流。

符号“o”是树叶，我的意思是，房间没有门可以进入较低层的其他房间。

为简单起见，每个房间都有两个门，但也可以有两个以上。

所以现在，最后想象一下:如果爆炸起源于属于父房间的任何子/叶房间，那么父房间的所有门将自动关闭，以防止爆炸传播到根父房间。

想象下表:

ROOM_ID | PARENT_ROOM | GATES_OPEN | EXPLOSION
    98       NULL          1         0
    99        98           1         0
   100        98           1         0
   102       100           1         0
   101       100           1         0 
   200       102           -         0
   201       102           -         0

所有房间的所有门都打开，因为最初没有爆炸。200和201房间没有门。

想象一下，每个房间都有一个传感器来检测可能发生的爆炸。如果传感器检测到爆炸，信号会传播到父房间，父房间会关闭所有门。该信号也向上传播到父房间，所有父房间也关闭所有门，依此类推，直到到达根父房间，根父房间也关闭所有门。

现在假设房间 ID:102 发生爆炸，所以我需要更新下表:

ROOM_ID | PARENT_ROOM | GATES_OPEN | EXPLOSION
    98       NULL          0         0
    99        98           1         0
   100        98           0         0
   102       100           1         1
   101       100           1         0 
   200       102           -         0
   201       102           -         0

那么使用递归 CTE，如何从初始表中获取更新的最终表？我需要将它从引起爆炸的根传播到根父级。

Answer 1

这是一种方法:

首先，创建并填充示例表(请在您以后的问题中为我们保存此步骤):

DECLARE @T AS TABLE
(
    ROOM_ID int,
    PARENT_ROOM int,
    GATES_OPEN bit,
    EXPLOSION bit
)


INSERT INTO @T VALUES
(98, NULL, 1, 0),
(99, 98, 1, 0),
(100, 98, 1, 0),
(102, 100, 1, 0),
(101, 100, 1, 0),
(200, 102, NULL, 0),
(201, 102, NULL, 0)

然后，创建 CTE:

DECLARE @RoomId int = 102;

;WITH CTE AS
(
    SELECT ROOM_ID
          ,PARENT_ROOM
          ,GATES_OPEN
          ,CAST(1 AS BIT) AS EXPLOSION
    FROM @T 
    WHERE ROOM_ID = @RoomId 
    UNION ALL
    SELECT t.ROOM_ID
          ,t.PARENT_ROOM
          ,CAST(0 AS BIT) AS GATES_OPEN
          ,t.EXPLOSION
    FROM @T t 
    INNER JOIN CTE ON t.ROOM_ID = CTE.PARENT_ROOM
)

更新表格:

UPDATE t 
SET GATES_OPEN = CTE.GATES_OPEN,
    EXPLOSION = CTE.EXPLOSION
FROM @T t
INNER JOIN CTE ON t.ROOM_ID = CTE.ROOM_Id

最后，测试更新是否OK:

SELECT *
FROM @T 

结果:

ROOM_ID PARENT_ROOM GATES_OPEN  EXPLOSION
98      NULL        0           0
99      98          1           0
100     98          0           0
102     100         1           1
101     100         1           0
200     102         NULL        0
201     102         NULL        0

更新

如果您不知道爆炸发生在哪个房间(我猜某些进程更新了数据库表并将爆炸值设置为 1)，那么您可以在表上使用触发器。和我之前写的查询差不多，结果也是一样的:

CREATE TRIGGER tr_Rooms_Update ON Rooms 
FOR UPDATE
AS

;WITH CTE AS
(
    SELECT ROOM_ID
          ,PARENT_ROOM
          ,GATES_OPEN
          ,EXPLOSION
    FROM inserted 
    WHERE EXPLOSION = 1
    UNION ALL
    SELECT t.ROOM_ID
          ,t.PARENT_ROOM
          ,CAST(0 AS BIT) AS GATES_OPEN
          ,t.EXPLOSION
    FROM Rooms t 
    INNER JOIN CTE ON t.ROOM_ID = CTE.PARENT_ROOM
)

UPDATE t 
SET GATES_OPEN = CTE.GATES_OPEN,
    EXPLOSION = CTE.EXPLOSION
FROM Rooms t
INNER JOIN CTE ON t.ROOM_ID = CTE.ROOM_Id

GO