Sql:t-sql parent-child Function Script

--parent-child reationship--2014-08-25--The location of the sub-nodes function list (including itself) if exists (SELECT * from dbo.sysobjects WHERE id = object_id ( N ' [dbo]. [Getbookplacechildrenid] ') and xtype in (n ' FN ', n ' IF ', n ' TF ')) drop function [dbo]. [Getbookplacechildrenid] Gocreate Function Getbookplacechildrenid (@ID int) Returns @Tree Table (bookplaceid int,bookplaceparent int, Bookplacename NVarchar (()) Asbegininsert @Tree Select bookplaceid,bookplaceparent, bookplacename from Bookplacelist Where Bookplaceid = @IDWhile @ @Rowcount > 0Insert @Tree Select a.bookplaceid, A.bookplaceparent, a.bookplacename from B Ookplacelist A Inner Join @Tree B on a.bookplaceparent = B.bookplaceid and A.bookplaceid not in (Select Bookplaceid from @ Tree)---returnendgoselect * FROM dbo. Getbookplacechildrenid (2)---Get the positional child node list ID address function (including itself) if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Getbookplacegroupid] ') and xtype in (n ' FN ', n ' IF ', n ' TF ')) drop function [dbo]. [Getbookplacegroupid] Gocreate Function [dbo]. [GetbookplacegroUPID] (@BookPlaceID int) RETURNS NVARCHAR ($) asbegindeclare @allstring NVARCHAR ($), @top NVARCHAR--, @ Bookplaceid int--set @BookPlaceID =2set @allstring = "Select @[email protected]+cast (bookplaceid as varchar (10)) + ', ' from [dbo]. [Getbookplacechildrenid] (@BookPlaceID) ORDER by Bookplaceid--where companyid<> @CompanyIDset @allstring =left (@allstring, LEN (@allstring)-1)--select @ Allstringreturn @allstringENDGOselect [dbo]. [Getbookplacegroupid] (2)----Check the location of all sub-nodes, with the path and sort if object_id (' Getbookplaceparentlevel ') is not null drop function Getbookplaceparentlevel go cre Ate function getbookplaceparentlevel (@id int) returns @re table (Bookplaceid int,bookplaceparent int,bookplacename Nvarc       Har (+), [level] int,sort varchar (+), Bookplacefullname nvarchar (+)) as BEGIN declare @l int set @l=0 Insert @re Select Bookplaceid,bookplaceparent,bookplacename,@l,right (' +ltrim ' (Bookplaceid), 3), Bookplacename fro M bookplacelist where [email protected] whiLe @ @rowcount >0 begin set @[email protected]+1 insert @re Select A.BOOKPLACEID,A.BOOKPL Aceparent,a.bookplacename,@l,b.sort+right (' +ltrim ' (A.bookplaceid), 3), b.bookplacefullname+ ' +a.BookPlaceName      From Bookplacelist as a, @re as B where b.bookplaceid=a.bookplaceparent and b.[level][email protected] End Update @re set [level] = "level" Return end go select * from getbookplaceparentlevel (0) SELECT * from GETBOOKP Laceparentlevel (0) where Bookplaceid<>1select * from Getbookplaceparentlevel (0) where bookplaceid<> 1select * from Getbookplaceparentlevel (0) where bookplaceid<>1 and [level]=1--Test Results/* 21 First floor 1001002 Tu ju Wen book location catalog first floor 31 second floor 1001003 Tu ju Wen book location directory second floor 42 first row 2001002004 Tu ju Wen book location catalog first Floor first row 62 second row 2001002006 Tu ju Wen book location catalog first floor second row 74 second floor 30 01002004007 the location catalogue of the first floor, the first row, the second floor, 84 the third floor, 3001002004008, the location of the first row of the third tier 54 first floor 3001002004005 of the book Location catalogue first Floor first Row first floor 124 fourth floor 30010020040 12 the location of the site catalogue of the first floor of the first floor, level fourth, level 96, Layer 3001002006009, first floor, first floor, second row, level 106, Level 30010020060.10 the location of the site catalogue of the first floor second row of the second tier 116 the third layer 3001002006011 the location of the book the first floor second row of the third floor */ 

 

DECLARE @id intset @id = 3;with T as--if the CTE is preceded by a statement, a semicolon partition is required (select Bookkindid, bookkindparent, Bookkindnamefrom bookkindlist where Bookkindid = @idunion allselect R1. Bookkindid,r1. Bookkindparent,r1. Bookkindnamefrom bookkindlist R1 join T as R2 on R1. bookkindparent = R2. Bookkindid) SELECT * from-t ORDER by bookkindid--Find all parent nodes with TAB as (select Bookkindid,bookkindparent,bookkindname from Bo okkindlist where bookkindid=3--child node union ALL select B.bookkindid,b.bookkindparent,b.bookkindname from tab a,--child node data set Bo Okkindlist B--parent node dataset where A.bookkindparent=b.bookkindid--child node dataset. Parendid= parent node Data set. ID) select * from tab; --Find all child nodes with the tab as (select Bookkindid,bookkindparent,bookkindname from bookkindlist where bookkindid=3--parent node union ALL s Elect b.bookkindid,b.bookkindparent,b.bookkindname from tab a,--parent data set Bookkindlist b--child node dataset where b.bookkindparent=a . Bookkindid--child node dataset. Id= parent node DataSet. Parendid) SELECT * from tab;--find path from child node to tier node with tab as (select Bookkindid,bookkindparent , Bookkindname,cast (Bookkindid as varchar) as Fulltypeid from Bookkindlist where bookkindid=3--child node union ALL select B.bookkindid,b.book Kindparent,b.bookkindname, cast (a.fulltypeid+ ', ' +cast (b.bookkindid as nvarchar) as varchar) as Fulltypeid From tab a,--child node dataset Bookkindlist B--parent node dataset where A.bookkindparent=b.bookkindid--child node dataset. Parendid= parent node DataSet. ID) SELECT * F Rom tab;

Sql:t-sql parent-child Function Script