在 SQL Server 2005 中没有游标的循环 [英] Loop through without Cursor in SQL Server 2005
问题描述
我有一个像这样的表格组织结构:
I have a table OrganisationStructure like this:
OrganisationID INT
ParentOrganisationID INT
OrganisationName VARCHAR(64)
1 | 0 | Company
2 | 1 | IT DIVISION
3 | 2 | IT SYSTEM BUSINESS UNIT
4 | 1 | MARKETING DIVISION
5 | 4 | DONATION BUSINESS UNIT
我想要查询,如果应用程序通过让我们说 OrganisatinID = 1
意味着它将循环(查看父/子)直到该表的末尾并捕获所有可能的返回 OrganisatioIDs = (1, 2, 3, 4, 5)
.
I want to have a query that if the app passing let say OrganisatinID = 1
means that it will loop (looking at parent/child) through till end of this table and grap all possible Returned OrganisatioIDs = (1, 2, 3, 4, 5)
.
其他如果通过 OrganisationID = 2
然后返回 OrganisationID = (2, 3)
Other if passing OrganisationID = 2
then Returned OrganisationID = (2, 3)
其他如果通过 OrganisationID = 3
然后返回 OrganisationID = 3
Other if passing OrganisationID = 3
then Returned OrganisationID = 3
有什么想法可以在没有光标的情况下做到这一点吗?
Any ideas to do this without cursor?
谢谢
推荐答案
您可以使用 SQL 2005 CTE 使 SQL 引擎递归执行此操作.
You can use SQL 2005 CTEs to make the SQL engine do it recursively.
基本方法的列举位于 http://blogs.msdn.com/anthonybloesch/archive/2006/02/15/Hierarchies-in-SQL-Server-2005.aspx
Celko 还有一本 SQL 书中的树,涵盖了所有这一切.
Celko also has a trees in SQL book which covers all of this to the nth degree.
或者您可以通过将每个级别选择到本地表变量中,然后循环,使用选择插入子级,直到您的@@ROWCOUNT 为零(即,您找不到更多子级)来强制执行它.如果您没有大量数据,这很容易编写代码,但是您通过说您不需要游标来暗示您正在寻找性能.
Or you can brute force it by selecting each level into a local table variable and then looping, inserting children with a select, until your @@ROWCOUNT is zero (i.e., you're not finding any more children). If you don't have a lot of data, this is easy to code, but you hinted that you're looking for performance by saying you dont want a cursor.
这篇关于在 SQL Server 2005 中没有游标的循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!