TSQL递归更新？ [英] TSQL A recursive update?

问题描述

我想知道tsql（CTE）中是否存在递归更新

I'm wondering if exists a recursive update in tsql (CTE)

ID  parentID value
--  -------- -----
1   NULL     0
2   1        0
3   2        0
4   3        0
5   4        0
6   5        0

我可以更新列 value 递归地使用例如CTE从ID = 6到最顶部的行？

I it possible to update the column value recursively using e.g CTE from ID = 6 to the top most row ?

推荐答案

是的。 MSDN给出了一个示例：

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;

这篇关于TSQL递归更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！