一、根据父Id 递归查询所有的子类Id
应用场景:
在树形组件的开关中会用到,如下图所示:
技术手段:用mysql函数实现,如下:
函数名:getSonIds
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(表Id) INTO sTempChd FROM 查询的表名 where FIND_IN_SET(父Id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
下面我用一张产品分类表(tbl_product_category)做例子,下图是它的字段
例子:1、先写好名为getSonIds的函数
CREATE DEFINER=`root`@`%` FUNCTION `getCommentIds`(rootId VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
DETERMINISTIC
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(comment_id) INTO sTempChd FROM ssc_posts_comment where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
2、mybatis xml中调用它
SELECT getSonIds(#{categoryPid})
二、根据子类Id 递归查询所有的父类 Id
技术手段:也是函数解决:
函数名:getParentIds
CREATE DEFINER=`root`@`%` FUNCTION `getCommentParentIds`(rootId VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE i varchar(100) default '';
DECLARE j varchar(1000) default rootId;
WHILE rootId is not null do
SET i =(SELECT parent_id FROM ssc_posts_comment WHERE comment_id = rootId);
IF i is not null THEN
SET j = concat(j, ',', i);
SET rootId = i;
ELSE
SET rootId = i;
END IF;
END WHILE;
return j;
END