--Table structure CREATE TABLE ' address ' (
' id ' int (one) not NULL auto_increment,
' Code_value ' varchar (+) DEFAULT NULL COMMENT ' area code ',
' Name ' varchar (+) DEFAULT NULL COMMENT ' zone name ',
' Remark ' varchar (+) DEFAULT NULL COMMENT ' description ',
' PID ' varchar (+) DEFAULT NULL COMMENT ' pid is Code_value ',
PRIMARY KEY (' id '),
KEY ' Ix_name ' (' name ', ' Code_value ', ' pid ')
) Engine=innodb auto_increment=1033 DEFAULT charset=utf8 comment= ' Administrative table ';
--mysql Implement tree structure query
--Method One create PROCEDURE sp_showchildlst (in Rootid varchar (20))
BEGIN
CREATE temporary TABLE IF not EXISTS tmplst
(Sno int PRIMARY key Auto_increment,code_value VARCHAR (), depth int);
DELETE from Tmplst;
Call Sp_createchildlst (rootid,0);
Select tmplst.*,address.* from tmplst,address where tmplst.code_value=address.code_value order by Tmplst.sno;
END CREATE PROCEDURE sp_createchildlst (in Rootid varchar (a), in Ndepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR (20);
DECLARE cur1 CURSOR for SELECT code_value from address where pid=rootid;
DECLARE CONTINUE HANDLER for don't FOUND SET done = 1;
INSERT into TMPLST values (null,rootid,ndepth); SET @ @max_sp_recursion_depth = 10;
OPEN Cur1;
FETCH Cur1 into B;
While done=0 do
Call Sp_createchildlst (b,ndepth+1);
FETCH Cur1 into B;
END while;
CLOSE Cur1;
end--method Two CREATE PROCEDURE sp_getaddresschild_list (in IDD varchar (36))
Begin
declare Lev Int;
Set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1 (code_value varchar), ' name ' varchar (+), PID varchar ($), levv INT);
INSERT tmp1 SELECT code_value, ' name ', pid,1 from address WHERE Pid=idd;
While Row_count () >0
Do
Set lev=lev+1;
INSERT tmp1 SELECT t.code_value,t. ' Name ', T.pid,lev from address T joins TMP1 A on t.pid=a.code_value and Levv=lev-1;
End while;
INSERT tmp1 SELECT code_value, ' name ', pid,0 from address WHERE Code_value=idd;
SELECT * from TMP1;
end--method Three Create FUNCTION fn_getaddress_childlist_test (rootid INT) RETURNS varchar (+) CHARSET UTF8 #rootId为你要查询的节点
begin# declaration of two temporary variables
DECLARE temp VARCHAR (1000);
DECLARE tempchd VARCHAR (1000);
SET temp = ' $ ';
SET Tempchd=cast (Rootid as CHAR); #把rootId强制转换为字符WHILE tempchd is not NULL does
SET temp = CONCAT (temp, ', ', tempchd); #循环把所有节点连接成字符串.
SELECT Group_concat (Code_value) to tempchd from address where Find_in_set (PID,TEMPCHD) >0;
END while;
RETURN temp;
end--method Four Create PROCEDURE sp_findaddresschild (IID varchar, layer bigint (20))
BEGIN
/* Create a temporary table to accept the query */
Create temporary table if not EXISTS tmp_table (ID varchar (), code_value varchar (), name varchar (+), PID varchar () EN Gine=innodb DEFAULT Charset=utf8;
/* Maximum allowable recursion number */
SET @ @max_sp_recursion_depth = 10;
Call Sp_iterativeaddress (Iid,layer);/* Core Data collection */
SELECT * from Tmp_table;/* Show */
Drop temporary table if exists tmp_table;/* Delete temp Table */
END
CREATE PROCEDURE sp_iterativeaddress (IID varchar), layer bigint (20))
BEGIN
DECLARE t_id INT;
DECLARE t_codevalue varchar () default IID;
DECLARE t_name varchar (character) set UTF8;
DECLARE t_pid varchar (character) set UTF8;
/* CURSOR Definition */
Declare cur1 CURSOR for select Id,code_value, ' name ', PID from address where pid=iid;
Declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET t_codevalue = null;
/* Allow recursion depth */
If Layer>0 Then
OPEN Cur1;
FETCH cur1 into T_id,t_codevalue,t_name,t_pid;
While (T_codevalue was not null)
Do
/* Core Data collection */
INSERT into tmp_table values (T_ID,T_CODEVALUE,T_NAME,T_PID);
Call Sp_iterativeaddress (T_CODEVALUE,LAYER-1);
FETCH cur1 into T_id,t_codevalue,t_name,t_pid;
END while;
End If;
end--method Five SQL implementation SELECT ' name ', Code_value as code_value,pid as parent ID, levels as parent to child progression, paths as parent to child path from (
SELECT ' name ', Code_value,pid,
@le: = IF (pid = 0, 0,
IF (LOCATE (CONCAT (' | ', PID, ': '), @pathlevel) > 0,
Substring_index (Substring_index (@pathlevel, CONCAT (' | ', PID, ': '),-1), ' | ', 1) +1
, @le + 1)) levels
, @pathlevel: = CONCAT (@pathlevel, ' | ', Code_value, ': ', @le, ' | ') pathlevel
, @pathnodes: = IF (pid = 0, ', 0 ',
Concat_ws (', ',
IF (LOCATE (CONCAT (' | ', PID, ': '), @pathall) > 0,
Substring_index (Substring_index (@pathall, CONCAT (' | ', PID, ': '),-1), ' | ', 1)
, @pathnodes), PID) paths
, @pathall: =concat (@pathall, ' | ', Code_value, ': ', @pathnodes, ' | ') Pathall
From address,
(SELECT @le: =0, @pathlevel: = ', @pathall: = ', @pathnodes: = ') vv
ORDER by Pid,code_value
) src
ORDER by PID
MySQL recursive query all child nodes, tree structure query
