mysql树

http://blog.itpub.net/26230597/viewspace-1269668/

http://blog.csdn.net/mchdba/article/details/39277301

DROP FUNCTION IF EXISTS fn_tree_pathname;;
CREATE FUNCTION fn_tree_pathname(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8   
BEGIN     
  DECLARE pathid VARCHAR(1000);   
  SET @pathid='';       
  CALL pro_cre_pnlist(nid,delimit,@pathid);   
  RETURN @pathid;   
END;


drop PROCEDURE pro_cre_pnlist;
CREATE PROCEDURE pro_cre_pnlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))
BEGIN                     
      DECLARE done INT DEFAULT 0;   
      DECLARE parentid INT DEFAULT 0;         
      DECLARE cur1 CURSOR FOR    
      SELECT t.parent_node_id,CONCAT(t.node_name,delimit,pathstr)   
        FROM tb_catalog_node AS t WHERE t.node_id = nid;   
           
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
      SET max_sp_recursion_depth=12;                     
       
      OPEN cur1;   
       
      FETCH cur1 INTO parentid,pathstr;   
      WHILE done=0 DO              
              CALL pro_cre_pnlist(parentid,delimit,pathstr);   
              FETCH cur1 INTO parentid,pathstr;   
      END WHILE;   
            
      CLOSE cur1;    
     END

select * from  tb_catalog_node;

select fn_tree_pathname(t.NODE_ID,'/') pathname ,t3.OFR_NAME ,
t3.OFR_CODE
from tb_catalog_node t INNER JOIN tb_catalog_ofr_rel t2
on t2.NODE_ID=t.NODE_ID INNER JOIN tb_prd_ofr t3
on t3.OFR_ID=t2.OFR_ID
 where t.NODE_ID=1000102;