Related Discussion Connections: http://expert.cbs.net/expert/topicview1.asp? Id = 1477009 Original: Table: Tree (id [integer], parentid [integer], remark [varchar])
INSERT INTO TREE (ID, PARENTID) SELECT 1,0 Union All Select 2,1 Union All SELECT 3,1 Union All SELECT 4,2 Union All Select 5,4 Union All SELECT 6,5 Union All SELECT 7,2
T (F1, ...) INSERT INTO T (F1) SELECT 1 Union All Select 5 Union All Select 3 Union All SELECT 4 Union All Select 6 Union All Select 4 Union All SELECT 5 Union All Select 3 Union All SELECT 4 Union All Select 1 Union All Select 7 Union All Select 6 Union All SELECT 4
Refer to the father and child relationship in the Tree table, the number of records of "ancestors" should include all "descendants" record number, statistics T table F1 each value record number ID counes1 152 103 24 85 46 27 2
Answers and simple analysis:
/ * I have seen the answers to the first few people, and it seems that the problem is complicated with "cursor", "temporary table", "recursive". "Cursor", "Temporary table" can not be used! "Recursive" is certainly the way to solve the thinking of the tree structure! But the Nested hierarchy of T-SQL can only go to 32! ICEVI (button factory) It is very advocated, although the ID, ParentID is sufficient to store only, but if it provides the form of performance, performance does not be too good! Many efficient tree structure forums are indeed stored and maintained at all nodes. Information of information, this shows only one SQL! Below is my reference answer, two custom function functions are almost the same, they are in front of the "hierarchical information": hierarchical information:
Method 1: UDF recursive implementation! There are 32 layers of nested restrictions * /
Alter Function DBo.get32ancestors (@x integer) returns varchar (250) asbegindeclare @ID integerDeclare @ReturnValue varchar (250)
Select Top 1 @ID = ParentIDFrom Tree Where [id] = @X
IF @ID <> @X begin select @RETURNVALUE = CAST (isnull (dbo.get32ancestors (@ID), '') as varchar) '-' cast (@x as varchar) endelse set @ReturnValue = @IDRETURN @ ReturnValueend
GO / * 2003-3-5 Method 2: No limit -3
2. Broken code Display -7-8-9-103.Getallancestors returns Null4.Getallancestors (root node) Returns 0- You 5. Dead loop point display: 4-5-6-4-8
* /
Alter Function Getallancestors (@x integer) returns varchar (1000) asbegindeclare @RETURNVALUE VARCHAR (1000) Declare @ID integerDeclare @parentID integer
Set @ID = -1
SELECT TOP 1 @ id = isnull ([ID], 0), @ ParentId = isnull ([ParentID], 0) from treewhere id = @X
While @ID <> @ParentID and @ParentID <> 0 and @ID> 0 and '-' isnull (@ReturnValue, '') '-' Not Like '% -' Cast (@ID as varchar) '-%' begin if @ReturnValue is not null set @returnValue = '-' @RETURNVALUE SET @ ReturnValue = Cast (@id as varchar) isnull (@ReturnValue, '') set @ID = -1 select top 1 @ Id = isnull ([ID], 0), @ ParentId = isnull ([ParentID], 0) from tree where id = @parentID END
Set @ReturnValue = '-' @returnValue
IF @ID> 0 set @ReturnValue = Cast (@ID as varchar) isnull (@RETURNVALUE, '')
IF @parentId = 0 or @ID = @parentid set @returnValue = '0-' isnull (@ReturnValue, '')
Return (@ReturnValue) - SELECT DBO.GETALLANCESTORS (10) End
Go
/ * Method One is "master" inertial thinking to complicate simple questions, "too tired"! Method 2 is that the ideas are simple and clear, not only "rookie" is preferred, "master" should also reflect! If this question is divided into two Q: 1. Ask each node level information 2. Significant records of each node to future generations
Maybe you will be inspired! The function is defined, and the following should be the same as the answer to the answers of the comrades of Icevi (button factory), and it is unable to do * /
SELECT ID, DBO.GETALLANCESTORS (ID), (SELECT Count (*) from T where '-' DBO.GETALLANCESTORS (F1) '-'% - ' Cast (Tree.id As Varchar) ' - % ') from tree
SELECT ID, DBO.GET32ANCESTORS (ID), (Select Count (*) from T where '- DBO.GET32ANCESTORS (F1) ' - 'Like' - '% -' Cast (Tree.id As Varchar) '- % ') from tree
/ * In addition, there should be a problem with the level of the package. Specific analysis, this question is not suitable for the definition function directly get the final result! The above answer is for reference only !! Welcome to participate in the discussion! * /
