带数据循环的SQL Server 2005递归查询-是否可能? [英] SQL Server 2005 recursive query with loops in data - is it possible?
问题描述
我有一个标准的老板/下属员工表.我需要选择一个老板(由ID指定)及其所有下属(及其下属等).不幸的是，现实世界中的数据存在一些循环(例如，两家公司的所有人都被对方设为老板).带有CTE的简单递归查询就此阻塞了(最大递归级别超过了100).仍然可以选择雇员吗?我不在乎选择它们的顺序，只是每个选择一次.
已添加:您要我的查询吗?嗯...好吧...我虽然很明显，但是-在这里:
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.
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
添加2:哦，如果不清楚，这是生产系统，我必须做一些升级(基本上添加某种报告).因此，如果可以避免，我宁愿不修改数据.
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.
推荐答案
我知道已经有一段时间了，但是我想我应该分享我的经验，因为我尝试了每种解决方案，以下是我的发现的摘要(也许这篇文章?):
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?):
- 用当前路径添加一列确实可以，但是性能受到影响，所以对我来说不是一个选择.
- 我找不到使用CTE的方法.
- 我编写了一个递归SQL函数，该函数将employeeIds添加到表中.为了避开循环引用，需要进行检查以确保没有重复的ID添加到表中.性能中等，但不理想.
完成所有这些工作后，我想到了将[合格的]雇员的整个子集转储为代码(C#)并使用递归方法对其进行过滤的想法.然后，我将过滤后的员工列表写入数据表，并将其作为临时表导出到我的存储过程中.令我难以置信的是，对于小型和较大的表(我尝试了最多35,000行的表)，这被证明是最快，最灵活的方法.
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).
这篇关于带数据循环的SQL Server 2005递归查询-是否可能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
