一個查詢出樹形結構下級或上級的SQL語句的書寫問題

1、一個查詢出樹形結構下級或上級的SQL 語句的書寫問題CREATE TABLE dbo.tbNode_List (nlIDvarchar (10) COLLATE Chinese_PRC_CI_AS NOT NULLPRIMARY KEY,-公司編碼OwnerNodeID varchar(10) COLLATE Chinese_PRC_CI_AS NULL ,-公司名稱nlName varchar (50) COLLATE Chinese_PRC_CI_ASNULL , - 上級公司編碼nlAddress varchar (100)COLLATE Chinese_PRC_CI_AS NULL-地

2、址) ONPRIMARYGOINSERT INTOtbNode_List(nlID,OwnerNodeID,nlName,nlAddress)VALUES(0001,0,某公司總部 ,北京 )INSERT INTOtbNode_List(nlID,OwnerNodeID,nlName,nlAddress)VALUES(0002,0001,西北營銷中心 ,)INSERT INTOtbNode_List(nlID,OwnerNodeID,nlName,nlAddress)VALUES(0003,0001,北京公司 ,)INSERT INTOtbNode_List(nlID,OwnerNodeID,

3、nlName,nlAddress)VALUES(0004,0003,北京海淀公司 ,)INSERT INTOtbNode_List(nlID,OwnerNodeID,nlName,nlAddress)VALUES(0005,0003,北京昌平公司 ,)gocreate functionf_getP(nlID varchar(10) returns re table(nlIDvarchar(10),lev int,ownerNodeID varchar(10),TFlagvarchar(10) asbegindeclare l intset l=0insert re select nlID,l,

4、ownerNodeID,本級 nlID fromtbNode_List where nlID = nlIDwhilerowcount>0beginset l=l+1insert re select a.nlID,l,a.ownerNodeID,下級 nlIDfrom tbNode_List a,re bwherea.ownerNodeID=b.nlID and b.lev=l-1 and a.nlID is notnullendupdate re set lev=l-levreturnendgocreate function f_getC(nlID varchar(10)returns

5、re table(nlID varchar(10),lev int,ownerNodeIDvarchar(10),TFlag varchar(10) asbegindeclare l intset l=0insert re select nlID,l,ownerNodeID,本級 nlID from tbNode_List where nlID = nlIDwhilerowcount>0beginset l=l+1insert re select a.nlID,l,a.ownerNodeID,上級 nlIDfrom tbNode_List a,re bwherea.nlID=b.ownerNodeID and b.lev=l-1 anda.ownerNodeID is not nullendupdate re setlev=l-levreturnendgodeclare nlID varchar(10)setnlID = 0003select * from dbo.f_getP(nlID)unionselect* from dbo.f_getC(nlID)drop function f_getP,f_getCdroptable tbNode_List/*nlIDlevo