我可以避免在此递归查找方案中使用游标吗?

时间:2021-11-21 08:35:44

I have a Table called Item. The design is like:

我有一个名为Item的表。设计如下:

ItemTable[
ItemID int,
ItemName varchar(50),
ParentItemId int ]

What I want is to find all the items that have an ancestor id and do some operation (e.g. delete, update).

我想要的是找到所有具有祖先id并执行某些操作的项目(例如删除,更新)。

Say I have six items,

说我有六件物品,

For Item 1, the parentItemId is null, 
for Item 2, the parentId is 1,
for Item 3, the parentId is 1
Item 4, parent id 2, 
item 5, parent id 2, 
item 6, parent id 4

I want to find all items that has an ancestor id 2. So should be 4,5, and 6.

我想找到所有具有祖先id 2的项目。所以应该是4,5和6。

In sql world, I can do it in a cursor. But wondering if it is something can be done using a clever CTE?

在sql世界中,我可以在游标中完成它。但是想知道它是否可以使用聪明的CTE来完成?

1 个解决方案

#1


3  

You can use recursive CTE:

您可以使用递归CTE:

;WITH cte(ItemID, ItemName,ParentItemId) AS (
   SELECT ItemID, ItemName, ParentItemId
   FROM #ItemTable
   WHERE ParentItemId IS NOT NULL
   UNION ALL
   SELECT i.ItemID, i.ItemName, t.ParentItemId
   FROM cte t 
   JOIN #ItemTable i 
     ON t.ItemID = i.ParentItemId
), cte2 AS                -- filter records with Ancestor = 2
(
  SELECT * 
  FROM cte
  WHERE ParentItemId = 2
)
UPDATE #ItemTable          -- UPDATE/DELETE operation
SET ItemName = CONCAT('Updated Name: ', i1.ItemName)
FROM #ItemTable i1
JOIN cte2 c2
  ON i1.ItemID = c2.ItemId;

SELECT *
FROM #ItemTable;

LiveDemo

LiveDemo

#1


3  

You can use recursive CTE:

您可以使用递归CTE:

;WITH cte(ItemID, ItemName,ParentItemId) AS (
   SELECT ItemID, ItemName, ParentItemId
   FROM #ItemTable
   WHERE ParentItemId IS NOT NULL
   UNION ALL
   SELECT i.ItemID, i.ItemName, t.ParentItemId
   FROM cte t 
   JOIN #ItemTable i 
     ON t.ItemID = i.ParentItemId
), cte2 AS                -- filter records with Ancestor = 2
(
  SELECT * 
  FROM cte
  WHERE ParentItemId = 2
)
UPDATE #ItemTable          -- UPDATE/DELETE operation
SET ItemName = CONCAT('Updated Name: ', i1.ItemName)
FROM #ItemTable i1
JOIN cte2 c2
  ON i1.ItemID = c2.ItemId;

SELECT *
FROM #ItemTable;

LiveDemo

LiveDemo