首先,需要了解两个MySQL的原生函数FIND_IN_SET和group_concat:
使用范例:
SELECT FIND_IN_SET('a','a,b,c,d');
运行结果:1;
SELECT group_concat(cdbh) FROM ss_menu;
运行结果:1,3,4,6,7,8,9,10,……等;
创建树形递归函数:
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(cdbh) INTO sTempChd FROM ss_menu
WHERE FIND_IN_SET(sjcd,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
/*查询此函数*/
select * FROM ss_menu m WHERE FIND_IN_SET(cdbh, func_menus(0)) ORDER BY sjcd;
返回的结果集如下所示: