SQL Server 2005递归查询在数据中有循环，这是可能的吗?
I've got a standard boss/subordinate employee table. I need to select a boss (specified by ID) and all his subordinates (and their subrodinates, etc). Unfortunately the real world data has some loops in it (for example, both company owners have each other set as their boss). The simple recursive query with a CTE chokes on this (maximum recursion level of 100 exceeded). Can the employees still be selected? I care not of the order in which they are selected, just that each of them is selected once.
我有一个标准的老板/下属员工表。我需要选择一个老板(由ID指定)和他的所有下属(以及他们的下属等)。不幸的是，现实世界的数据中有一些循环(例如，两个公司的所有者都将对方设置为他们的老板)。使用CTE阻塞的简单递归查询(最大递归级别超过100)。还可以选择员工吗?我不关心它们被选择的顺序，只关心它们每一个被选择一次。
Added: You want my query? Umm... OK... I though it is pretty obvious, but - here it is:
with
UserTbl as -- Selects an employee and his subordinates.
(
select a.[User_ID], a.[Manager_ID] from [User] a WHERE [User_ID] = @UserID
union all
select a.[User_ID], a.[Manager_ID] from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID])
)
select * from UserTbl
Added 2: Oh, in case it wasn't clear - this is a production system and I have to do a little upgrade (basically add a sort of report). Thus, I'd prefer not to modify the data if it can be avoided.
10 个解决方案
#1
2
I know it has been a while but thought I should share my experience as I tried every single solution and here is a summary of my findings (an maybe this post?):
我知道已经有一段时间了，但我想我应该分享我的经验，因为我尝试了每一个解决方案，这里是我的发现的总结(也许是这个帖子?)
- Adding a column with the current path did work but had a performance hit so not an option for me.
- 添加一个包含当前路径的列确实有效，但是对我来说没有一个选项。
- I could not find a way to do it using CTE.
- 我找不到使用CTE的方法。
- I wrote a recursive SQL function which adds employeeIds to a table. To get around the circular referencing, there is a check to make sure no duplicate IDs are added to the table. The performance was average but was not desirable.
- 我编写了一个递归SQL函数，将employeeIds添加到表中。要绕过循环引用，需要进行检查，以确保表中没有添加重复id。表现一般，但并不理想。
Having done all of that, I came up with the idea of dumping the whole subset of [eligible] employees to code (C#) and filter them there using a recursive method. Then I wrote the filtered list of employees to a datatable and export it to my stored procedure as a temp table. To my disbelief, this proved to be the fastest and most flexible method for both small and relatively large tables (I tried tables of up to 35,000 rows).
完成了所有这些之后，我想到了一个主意，将所有的[合格的]员工子集都写进(c#)中，并使用递归方法对它们进行筛选。然后，我将经过过滤的雇员列表写入一个datatable，并将其作为临时表导出到存储过程。令我难以置信的是，对于小表和相对较大的表，这被证明是最快和最灵活的方法(我尝试了多达35,000行的表)。