اگر در SQL Server 2000 کار میکنید، این راهشه (ولی در 2005 روشهای بهتری وجود داره):
use northwind
go
DECLARE @ManagerID int
SET @ManagerID =2
--holds the output treelevel lets us isolate a level in the looped query
DECLARE @outTable table (employeeId int, ManagerID int, treeLevel int, processed bit)
--used to hold the level of the tree we are currently at in the loop
DECLARE @treeLevel as int
SET @treelevel = 1
--get the top level
INSERT into @outTable
SELECT employeeId, ReportsTo, @treelevel as treelevel, 0
FROM dbo.employees as employees
WHERE (employees.ReportsTo = @ManagerID)
WHILE (1 = 1) --imitates do...until construct
BEGIN
INSERT INTO @outTable
SELECT employees.employeeId, employees.ReportsTo,
ht.treelevel + 1 as treelevel,0
FROM dbo.employees as employees
JOIN @outTable as ht
ON employees.ReportsTo = ht.employeeId
--this where isolates a given level of the tree
WHERE ht.processed=0
IF @@rowcount = 0 BREAK
UPDATE @outTable SET processed=1
WHERE treeLevel-1<@treelevel
SET @treelevel = @treelevel + 1
END
--now look at the data in context of the employees we have inserted.
select * from @outTable
قوانین ایجاد تاپیک در تالار
