mysql 递归查询

相关函数

String Operators

FIND_IN_SET()

Return the index position of the first argument within the second argument

CONCAT()

Return concatenated string

Aggregate (GROUP BY) Functions

GROUP_CONCAT()  Return a concatenated string

mysql> SELECT student_name,GROUP_CONCAT(test_score)

       FROM student

       GROUP BY student_name;  

Or:

mysql> SELECT student_name,

         GROUP_CONCAT(DISTINCT test_score

                      ORDER BY test_score DESC SEPARATOR ' ')

       FROM student

       GROUP BY student_name

  

The default separator between values in a group is comma (,).

查询所有子节点，包括当前节点，用，分隔，，限制循环层级最大为10，保证始终可退出递归

CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(rootId bigint) RETURNS varchar(2000) CHARSET utf8

BEGIN

    declare str varchar(2000);

    declare cid varchar(2000);

    declare level_count int;

   

    set str = '';

    set cid = cast(rootId as char);

    set level_count = 0;

   

    while cid is not null and level_count<10 do

set str = concat(str,',',cid);

        select group_concat(id) into cid from product_category where find_in_set(parent_id,cid)>0;

       

        set level_count = level_count+1;

       

end while;

    return substring(str,2);

END

CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(nodeId bigint) RETURNS varchar(2000) CHARSET utf8

BEGIN

     declare pid bigint default null;

    declare str varchar(2000) default nodeId;

   

    while (nodeId is not null)

    do

set pid=(select parent_id from product_category where id=nodeId);

   

if pid is not null then

set str = concat(str,',',pid);

end if;

set nodeId=pid;

   

    end while;

RETURN str;

END