mysql-Recursive Query method parsing

mysql- Recursive Query method parsing , Brother Lian Education ( www.lampbrother.net ) to help you make a simple arrangement: If you need a friend, you 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);DECLARESTCC VARCHAR (n); # give St an initial value cannot be null,null There will be no return value set st= ";set stcc=id;# determines Whether the STCC is null While STCC was not NULL do# stitching string 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

This article is from the "11913030" blog, please be sure to keep this source http://11923030.blog.51cto.com/11913030/1834720

mysql-Recursive Query method parsing