CREATE DEFINER=`root`@`%` PROCEDURE `get_pipe_child`(IN `in_pipe2Num` varchar(25),IN `in_sectionNum` varchar(5))
BEGIN

?declare ids varchar(1000) default '';?
?declare tempids varchar(1000);?
?
?-- 先根據(jù)標(biāo)段號(hào)查詢出數(shù)據(jù)組成臨時(shí)表
?DROP TEMPORARY TABLE IF EXISTS temp_weld_position;
?CREATE TEMPORARY TABLE temp_weld_position AS
?select t1.id,t1.section_num,t1.weld_code,t1.lon,t1.lat,t2.pipe1_num,t2.pipe2_num from
?? ?(select id,section_num,weld_code,lon,lat from weld_position where section_num = in_sectionNum and LENGTH(lon)>=7 and LENGTH(lat)>=6 and is_deleted=0) t1
?? ?join (select id,weld_code,pipe1_num,pipe2_num from weld_manage where section_num = in_sectionNum) t2 on t1.weld_code=t2.weld_code;

?-- 在根據(jù)傳入的pipe2_num 遞歸查詢出所有的數(shù)據(jù)，將pipe2_num當(dāng)做id，pipe1_num當(dāng)pid
?set tempids = in_pipe2Num;?
?while tempids is not null do?
? set ids = CONCAT_WS(',',ids,tempids);?
? select GROUP_CONCAT(pipe2_num) into tempids from temp_weld_position where FIND_IN_SET(pipe1_num,tempids)>0; ?
?end while;?
?
? ?select t1.id,t1.section_num,t1.weld_code,t1.lon,t1.lat,t2.pipe1_num,t2.pipe2_num from
? ?(select id,section_num,weld_code,lon,lat from weld_position where section_num = in_sectionNum and LENGTH(lon)>7 and LENGTH(lat)>6 and is_deleted=0) t1
? ?join (select id,weld_code,pipe1_num,pipe2_num from weld_manage where section_num = in_sectionNum) t2 on t1.weld_code=t2.weld_code
? ?where FIND_IN_SET(t2.pipe2_num,ids)
? ?order by FIND_IN_SET(t2.pipe2_num,ids);?? ??? ??? ??? ?
END