پیمایش عمقی جداول درختی

[Galawij]

سلام
برای پیمایش عمقی جداولی که به صورت درختی(یک ارتباط یک به چند به خود جدول)تعریف شده اند چه راه کاری پیشنهاد می کنید؟
پیمایش دو سطحی و در ادامه چند سطحی

[siagripsy]

سلام . 2 راه برای این کار وجود داره . راه اول اینه که در این جداول از نوع داده HirarchyID استفاده کنی . که ایده آل ترین و بهترین راهشه . البته یه مشکل داره اونم اینکه اگه بعداً بخوای DBMS رو به مثلاً Oracle تغییر بدی مشکل داری . چون این نوع داده مخصوص SQL هستش .
راه دوم هم این هست که خودت با استفاده از کرسر روی رکوردها لوپ بزنی و خودت با while یه جوری ریکرسیو کوئری رو handle کنی که خوب هم کندتره و هم یکم برای اونایی که به ریکرسیو کوئری عادت ندارند سخت تره ، ولی مشکل بالایی رو نداره .

[یوسف زالی]

سلام.
فرض کنیم درخت ما این باشه:

72529

این کد پیمایش اول سطح هست:


create table #T(SN int, DAD int)
insert into #T
values
(1, 0),
(9, 11),
(2, 1),
(5, 1),
(11, 5),
(3, 2),
(6, 11),
(4, 2),
(8, 2),
(7, 3),
(10, 5)
declare @t table(ID int, Father int)
declare @end int
set @end = 1
insert into @t
select*
from #T
where DAD = 0
while @end <> 0
begin
insert into @t
select*
from #T
where DAD in(select ID from @t)and SN not in(select ID from @t)

set @end =@@ROWCOUNT
end
select*
from @t
drop table #T


روشهای بهتری هم حتما هست که من بلد نیستم.
دارم رو LRN و NLR کار می کنم.
احتمالا یه چیزی بتونم در بیارم.

[یوسف زالی]

متاسفانه برای LRN محدودیت وجود داره!
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

اما کد:



create table #T(SN int, DAD int)
insert into #T
values
(1, 0),
(9, 11),
(2, 1),
(5, 1),
(11, 5),
(3, 2),
(6, 11),
(4, 2),
(8, 2),
(7, 3),
(10, 5)
create table #Result(ID int, Father int)
go
create procedure #P(@Node int)
as
begin
declare @S varchar(max)
set @S =''

insert into #Result
select*
from #T
where SN = @Node

select @S +='exec #P '+cast(SN as varchar(5))+'; '
from #T
exec(@S)
end
go
exec #P1
select*
from #Result
drop table #T
drop table #Result
drop procedure #P

[Galawij]

ممنونم دوست من yousijoon (http://barnamenevis.org/member.php?70247-yousijoon)
ولی خواهشاً کد ها را کپی پیست نکنید هیچی معلوم نیست. اگر امکان تایپش نیست حداقل ازش یک عکس بگیرید و یا اینکه داخل تگ های Sql قرار ندید. مچکرم.


راه اول اینه که در این جداول از نوع داده HirarchyID استفاده کنی
این نوع داده ای که می فرمائید تو نسخه 2008 هست؟
من با 2005 کار می کنم نوع داده ای به این اسم ندیدم!

[یوسف زالی]

این کد اول:
72542

این هم دومی:
72544

[Galawij]

ممنون از دوستان
این مقاله هم خیلی کمک می کنه:

1 Introduction

As a DBA, I am often required by clients to look into a database "created by a previous DBA who has left the company". Among various jobs, one is to document the table relationships in the database because "the original documents are not updated to reflect the current status of the database." What I usually do is to create a tree-format figure in my final report. This serves as a quick reference of the table relationship diagram. A simple example is as follows
grandparent
|---parent
|---child
|---grandchild
|---uncle
|---cousin

To finish my work as easily as possible, my idea is to generate this type of text diagram in Query Analyzer (QA), and then all I need to do is to copy & paste the result to my word document.
2 Implementation

To accomplish my task, I need to use sysforeignkeys table, which exists in every database and contains all the foreign key information. See BOL for the detailed table structure of sysforeignkeys.
The implementation consists of two steps:
Step 1: I composed a stored procedure that accepts a table name as an input parameter, then displays all the children tables in a tree structure with the table at the top level. This is the core step
Step 2: I wrote a script to loop through each distinct referenced table ( (object_name(rkeyid) ) in the sysforeignkeys with a cursor, and call the stored procedure composed in Step 1.
Here I will only discuss the stored procedure (SP) in Step 1 because the script in Step 2 is easy and obvious to implement. There are two key points in the SP, one is about recursion in T-SQL and another is about SQL Server cursor which will be discussed later.

3 Stored Procedure Details

A brief description of the stored procedure algorithm is as follows:
1. When a table has child tables, create a cursor to loop through each child table
2. For each child table, recursively call the stored procedure

create proc usp_DisplayTableRelation
@table_name varchar(100) -- the procedure will try to find all the child tables for the table @table_name
, @space_len int = -4 output -- for printing position purpose
as
begin -- sp
declare @child varchar(100)
set @space_len = @space_len + 4

if @table_name is null
begin -- return
set @space_len = @space_len - 4
return
end -- return

if not exists (select * from sysforeignkeys where rkeyid = object_id(@table_name) )
begin -- leaf level
if @space_len <= 0
print @table_name
else
print space(@space_len) + '|---' + @table_name

set @space_len = @space_len - 4
return
end -- leaf level
else -- the @table_name table exists
begin -- else
if @space_len <= 0
print @table_name
else
print space(@space_len) + '|---' + @table_name

if exists ( select * from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid = fkeyid) -- self referenced
print space(@space_len) + '|---' + @table_name

declare curChild cursor local for
select object_name(fkeyid) as child from sysforeignkeys
where rkeyid = object_id(@table_name) and rkeyid <> fkeyid

open curChild
fetch next from curChild into @child

while @@fetch_status = 0
begin -- cursor loop
exec usp_DisplayTableRelation @table_name = @child, @space_len = @space_len output
fetch next from curChild into @child
end -- cursor loop

close curChild
deallocate curChild
end --else
set @space_len = @space_len - 4
return
end --sp

3.1 Recursion in T-SQL

Generally speaking, recursion is the most concise and code-efficient way to write a function dealing with traversing hierarchy structure. When considering a recursive procedure, we have to consider two questions:
1. What is the exit condition for the procedure?
2. Does each recursive call to the procedure involve a small case of the original problem?
In this case, the answers to the two questions are:
1. When a table has no children (lowest hierarchy level, i.e. a leaf) , in another word, the table is not referenced by any other tables, the SP exits.
2. Yes, each recursive call receives the name of a table at the lower hierarchy level.
Because in SQL 2000, the recursive calls can be nested up to 32 levels, so if the relationship tree has more than 32 levels in depth, an error will occur. This is very rare however.
3.2 Cursor in T-SQL

In SQL Server 7.0 / 2000, there are two types of cursor, local and global. The global cursor is in the context of connection while the local cursor is specific to the procedure where it is created. So we have to explicitly define the cursor in the SP as local cursor, otherwise, when a recursive call is made, if the cursor is global, an error will occur as there already exists a global cursor with the same name, which was created in the procedure of the outside level.
4 Summary

In this article, we have discussed how to use sysforeignkeys to quickly display the relationship of a table and its child tables. With very small changes ( just changing the select statement in the cursor definition ), we can also display the relationship of a table and its parent tables. This is a very useful tool for DBAs to quickly review the table relationships in a database.