"Go" SQL recursive query problem

Original link Address http://www.cnblogs.com/sweting/archive/2009/06/08/1498483.html

Encountered a problem in the work, is the need for SQL recursive query. Don't understand, so to csdn up to ask, where the efficiency is really very high, I have not asked questions on it before. Problem Description: I have a table structure as follows:
ID Upperid
1 2
3 2
4 1
5 3 Level I do not know, I want to use a recursive SQL statement to all the data belonging to a certain upperid, including its subtree, are read out, how should the child write?
such as Upperid =2.
So first find 1, 3, then 1, 3 find 4,5 using SQL statement implementation There are two friends who gave the reply: Fa_ge (Tsurumi Tsunami nine days)Create table T (id int,upperid int)
INSERT INTO T
Select 1, 2
UNION ALL select 3, 2
UNION ALL Select 4, 1
UNION ALL Select 5, 3
SELECT * FROM t
Create function AA (@upperid int)
Returns @t table (ID int,upperid int,level int)
As
Begin
DECLARE @i int
Set @i=1
INSERT INTO @t
Select *,@i from t where [email protected]
While @ @rowcount >0
Begin
Set @[email protected]+1
INSERT INTO @t
Select A.*,@i from t a LEFT join @t B on a.upperid=b.id
where [email protected]
End
Return
Endselect * from DBO.AA (1) ID Upperid level
----------- ----------- -----------
4 1 1 (the number of rows affected is 1 lines) SELECT * from DBO.AA (2) ID Upperid level
----------- ----------- -----------
1 2 1
3 2 1
4 1 2
5 3 2 (the number of rows affected is 4 rows)
This need level this number, otherwise not get. Hellowork (one or two breeze)----Create test data
If object_id (' Tbtest ') is not null
drop table Tbtest
If object_id (' Spgetchildren ') is not null
drop proc Spgetchildren
GO
CREATE TABLE tbtest (id int, Upperid int)
Insert Tbtest
Select 1, 2 UNION ALL
Select 3, 2 UNION ALL
Select 4, 1 UNION ALL
Select 5, 3
GO
----Create a stored procedure
create proc Spgetchildren @id int
As
Declare @t table (ID int)
Insert @t Select ID from tbtest where Upperid = @id
While @ @rowcount > 0
Insert @t Select a.ID from Tbtest as a inner join @t as B
On a.upperid = b.ID and a.ID No in (select ID from @t)
SELECT * FROM @t
GO----Execute Stored procedure
DECLARE @upperid int
Set @upperid = 2
EXEC Spgetchildren @upperid----Clear test environment
drop proc Spgetchildren
drop TABLE tbtest/* Results
Id
-----------
1
3
4
5
*/
This is in line with my request. But what I need is a function that is rewritten as follows; Create function GetChildren (@id varchar (20))
Returns @t table (ID varchar (20))
As
Begin
Insert @t Select Wayid from TB where Upperwayid = @id
While @ @rowcount > 0
Insert @t Select A.wayid from TB as a inner join @t as B
On A.upperwayid = b.ID and A.wayid No in (select ID from @t)
Return
End Haha, that's really cool. CSDN problem Address: http://community.csdn.net/Expert/topic/5731/5731880.xml?temp=.8160211 originally in order to solve this problem, originally wanted to use recursion, on the Internet to see the following information:

The table structure is like this

Department Parent Department
A B
B C
C D
A A
b b
C C

Ask for an SQL statement, according to a check its parent department, the query result is
Parent Department
B
C
D

=================================================

with function
CREATE TABLE TB (department varchar (20), Parent department varchar (20))

INSERT into TB select ' A ', ' B ' union ALL select ' B ', ' C ' UNION ALL SELECT ' C ', ' D '
UNION ALL SELECT ' A ', ' a ' union ALL select ' B ', ' B ' union ALL SELECT ' C ', ' C '

--select * from TB
Create function Test_f (@name varchar (20))
Returns @ta table (Superior Department varchar (20))
As
Begin
--select @name = Parent Department from TB where department [email protected] and Department! = Parent Department
While exists (select 1 from TB where department [email protected] and Department! = Parent Department)
Begin
Insert @ta Select Parent Department from TB where department [email protected] and Department! = Parent Department
Select @name = Parent Department from TB where department [email protected] and Department! = Parent Department
End
Return
End

SELECT * from Dbo.test_f (' A ')

Delete:
Drop function Test_f
DROP table TB

Parent Department
--------------------
B
C
D

(The number of rows affected is 3 rows)

(Transferred from: http://blog.csdn.net/jackeyabc/archive/2007/03/19/1533775.aspx)

But it can be from the department to the superior department, but do not know how to change from the upper department to the department.