The implementation method of database SQL recursive query in different database _mssql

This article introduces the database SQL recursive query in different database implementation methods, the specific contents please see below.

For example, the table structure data is as follows:

Table:tree

ID Name ParentID

11 Level 0

22 Level 1

33 Level 2

44 Level 3

SQL SERVER 2005 Query method:

Check with
Tmptree as
(
  select * from tree where id=2
  UNION ALL
  Select p.* from Tmptree inner JOIN Tree p in P.id=tmptree.parentid
)
select * from Tmptree
 
//Under Check with
Tmptree
as
(
  SELECT * from tree where id=2
  UNION ALL
  Select s.* from Tmptree inner join tree s on s.parentid=tmptree.id
)
SELECT * from Tmptree

SQL SERVER 2008 and later versions, you can also use the following methods:

Add a column of Tid, the type is set to: HierarchyID (This is the CLR type, representing the hierarchy), and the ParentID field is canceled, which becomes as follows: (table name: TREE2)

TId Id Name

0x level 11
0x58 level 22
0X5B40 Level 33
0X5B5E Level 44

Query method:

Select *,tid.getlevel () as [level] from Tree2--gets all levels
DECLARE @ParentTree HierarchyID
SELECT @ParentTree =tid From Tree2 where id=2
SELECT *,tid.getlevel () as [level] from Tree2 WHERE tid.isdescendantof (@ParentTree) =1- Gets all subordinate DECLARE of the specified node
@ChildTree hierarchyid
Select @ChildTree =tid from Tree2 WHERE id=3
SELECT *, Tid.getlevel () as [levels] from Tree2 WHERE @ChildTree. Isdescendantof (TId) =1--gets all the ancestors of the specified node

Query methods in Oracle:

SELECT * from
tree START and id=2
CONNECT by PRIOR id=parentid--Check
select
START with id=2
CONNECT by id= PRIOR ParentID--check

The Query method in MYSQL:

Defines a list of strings that query all parent IDs for this specified ID based on ID, comma-delimited
CREATE definer= ' root ' localhost ' FUNCTION ' getchildlst ' (rootid int, direction int) RETURNS varchar (1000) CHARSET UTF8
BEGIN
 DECLARE stemp (varchar);
  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 the stempchd from the tree WHERE Id=rootid;
  End IF;
  While stempchd isn't null do
    SET stemp = concat (stemp, ', ', stempchd);
    SELECT Group_concat (ID) into the 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 (1,1));
In_set (Id,getchildlst (1,2));--Check up

Add: The above method in the next check is no problem, but there will be problems in the search, the reason is that my logic is wrong, there is a dead loop, has been amended, the new method is as follows:

CREATE definer= ' root ' @ ' localhost ' FUNCTION ' getchildlst ' (rootid int,direction int) RETURNS varchar (1000) CHARSET utf8< C1/>begin
 DECLARE stemp VARCHAR (5000);
  DECLARE stempchd VARCHAR (1000);
  SET stemp = ' $ ';
  SET stempchd =cast (Rootid as CHAR);
  
  IF direction=1 THEN while
  stempchd isn't 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 are 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 is convenient for recursive queries.