在 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屋！