mysql-Recursive Query method parsing

mysql-recursive Query method analysis, brother even education to help you do a simple collation: the need for friends can refer to ha.

First of all

Table structure and data

CREATE TABLE ' class ' (' ClassID ' int (one) not null auto_increment, ' Banji ' int (one) ' DEFAULT NULL COMMENT ' 0 ', ' Nianji ' VA Rchar (255) Default NULL, PRIMARY KEY (' ClassID ')) Engine=innodb auto_increment=11 default Charset=utf8;---------------- ----------------Records of class------------------------------insert INTO ' class ' VALUES (' 1 ', ' 0 ', ' 1 '); INSERT INTO ' cl "Values (' 2 ', ' 1 ', ' 2 '); INSERT INTO ' class ' Values (' 3 ', ' 1 ', ' f '); insert INTO ' class ' Values (' 4 ', ' 2 ', ' d '); Insert I NTO ' class ' Values (' 5 ', ' 3 ', ' s '); insert INTO ' class ' values (' 6 ', ' 0 ', ' a ') and insert INTO ' class ' values (' 7 ', ' 6 ', ' Q '); Nsert into ' class ' values (' 8 ', ' 7 ', ' h '); insert INTO ' class ' values (' 9 ', ' 5 ', ' sum ' ); insert INTO ' class ' VALU ES (' Ten ', ' 4 ', ' 121 ');

Method parsing 1

First level write a query result unite

SELECT * from class where ClassID =1unionselect * from class where Banji in (SELECT ClassID from class where ClassID =1) U Nion SELECT * from class where Banji in (select ClassID from class where Banji in (select ClassID from class where ClassID =1))

Method Parsing 2

Using function methods

Create a function to get the value of a node

CREATE FUNCTION ' Selecttree ' (' id ' int) RETURNS varchar (a) BEGIN #Routine body goes here ... DECLARE St VARCHAR (4000); DECLARE STCC VARCHAR (n); # give St the initial value cannot be null,null will not return set st= "; Set stcc=id;# determines if STCC is null and STCC is not null do# stitching string is assigned to St SET St=concat (St, ', ', STCC); SELECT Group_concat (ClassID) to STCC from class where Find_in_set (BANJI,STCC) >0; END while; RETURN St; END;

Call function to isolate results

SELECT * from class where ClassID in (SELECT A.ban from class A,class B where A.banji=b.classid) and banji=0

mysql-Recursive Query method parsing