Implementation methods of Database SQL recursive queries in different databases, SQL Recursion

Implementation methods of Database SQL recursive queries in different databases, SQL Recursion

This article describes how to implement SQL recursive queries in different databases. For more information, see the following.

For example, the table structure data is as follows:

Table: Tree

ID Name ParentId

1 Level 1 0

2 Level 2 1

3 level 3 2

4 Level 4 3

SQL SERVER 2005 query method:

// Check with tmpTreeas (select * from Tree where Id = 2 union all select p. * from tmpTree inner join Tree p on p. id = tmpTree. parentId) select * from tmpTree // check with tmpTreeas (select * from Tree where Id = 2 union all select s. * from tmpTree inner join Tree s on s. parentId = tmpTree. id) select * from tmpTree

For SQL SERVER 2008 and later versions, the following methods are also available:

Add a column of TID with the type set to hierarchyid (this is the CLR type, indicating the level), and cancel the ParentId field to the following: (Table Name: Tree2)

TId Id Name

0x1 Level 1
0x58 2 Level 2
0x5B40 3 level 3
0x5B5E 4 level 4

Query Method:

SELECT *, TId. getLevel () as [level] FROM Tree2 -- get all levels DECLARE @ ParentTree hierarchyidSELECT @ ParentTree = TId FROM Tree2 WHERE Id = 2 SELECT *, TId. getLevel () AS [level] FROM Tree2 WHERE TId. isDescendantOf (@ ParentTree) = 1 -- get DECLARE @ ChildTree hierarchyidSELECT @ ChildTree = TId FROM Tree2 WHERE Id = 3 SELECT *, TId. getLevel () AS [level] FROM Tree2 WHERE @ ChildTree. isDescendantOf (TId) = 1 -- obtain all superiors of a specified Node

Query Method in ORACLE:

SELECT * FROM TreeSTART WITH Id = 2 connect by prior id = ParentId -- Query SELECT * FROM TreeSTART WITH Id = 2 connect by id = PRIOR ParentId -- Query

MYSQL query method:

// Define a string list that queries all the IDS specified by the parent ID based on the ID, separated by commas (,) create definer = 'root' @ 'localhost' FUNCTION 'getchildlst' (rootId int, direction int) RETURNS varchar (1000) CHARSET utf8BEGIN DECLARE sTemp VARCHAR (5000); DECLARE sTempChd VARCHAR (1000); SET sTemp = '$ '; IF direction = 1 then set sTempChd = cast (rootId as CHAR); ELSEIF direction = 2 then select cast (ParentId as CHAR) into sTempChd FROM Tree WHERE Id = rootId; end if; WHILE sTempChd is not null do set sTemp = concat (sTemp, ',', sTempChd); SELECT group_concat (id) INTO sTempChd FROM Tree where (direction = 1 and FIND_IN_SET (ParentId, sTempChd)> 0) or (direction = 2 and FIND_IN_SET (Id, sTempChd)> 0); end while; RETURN sTemp; END // query method: select * from tree where find_in_set (id, getChildLst (); -- Query select * from tree where find_in_set (id, getChildLst (); -- Query

Note: There is no problem with the above method in the next query, but there will be a problem during the query, because my logic is wrong, there is an endless loop, and it has been corrected. The new method is as follows:

CREATE DEFINER=`root`@`localhost` FUNCTION `getChildLst`(rootId int,direction int) RETURNS varchar(1000) CHARSET utf8BEGIN DECLARE sTemp VARCHAR(5000);  DECLARE sTempChd VARCHAR(1000);  SET sTemp = '$';  SET sTempChd =cast(rootId as CHAR);    IF direction=1 THEN  WHILE sTempChd is not null DO    SET sTemp = concat(sTemp,',',sTempChd);    SELECT group_concat(id) INTO sTempChd FROM Tree where FIND_IN_SET(ParentId,sTempChd)>0;  END WHILE;  ELSEIF direction=2 THEN  WHILE sTempChd is not null DO    SET sTemp = concat(sTemp,',',sTempChd);    SELECT group_concat(ParentId) INTO sTempChd FROM Tree where FIND_IN_SET(Id,sTempChd)>0;  END WHILE;  END IF;RETURN sTemp;END

This facilitates recursive queries.