带有数据循环的 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 函数，它将员工 ID 添加到表中.为了绕过循环引用，需要检查以确保没有重复的 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屋！
