欢迎光临
一个有态度、有温度的分享型博客

MySQL实现Oracle中的SYS_CONNECT_BY_PATH功能

MySQL实现Oracle中的SYS_CONNECT_BY_PATH功能:

准备建表SQL

DROP TABLE IF EXISTS `tb_dict_category_config`;
CREATE TABLE `tb_dict_category_config` (
  `id` varchar(100) DEFAULT NULL,
  `pid` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `isparent` varchar(6) DEFAULT 'false'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_dict_category_config
-- ----------------------------
INSERT INTO `tb_dict_category_config` VALUES ('103', '12', '分类60', 'false');
INSERT INTO `tb_dict_category_config` VALUES ('1', '0', '全部分类', 'true');
INSERT INTO `tb_dict_category_config` VALUES ('12', '1', '分类6', 'true');

接下来是创建函数getParentList的SQL:

CREATE FUNCTION `getParentList` (rootId VARCHAR (50)) RETURNS VARCHAR (1000) 
BEGIN
DECLARE sParentList VARCHAR (1000) ;
DECLARE sParentTemp VARCHAR(1000); 
DECLARE curName VARCHAR(1000);
SET sParentTemp =CAST(rootId AS CHAR); 
WHILE sParentTemp IS NOT NULL DO 
select name into curName from tb_dict_category_config where id = sParentTemp;
IF (sParentList IS NOT NULL) THEN 
SET sParentList = CONCAT(curName,'->',sParentList); 
ELSE 
SET sParentList = CONCAT(curName); 
END IF; 
SELECT GROUP_CONCAT(pid) INTO sParentTemp FROM tb_dict_category_config WHERE FIND_IN_SET(id,sParentTemp)>0; 
END WHILE; 
RETURN substring(sParentList,13); 
END

执行结果:

select getParentList(103);

如下图:

参考:

  1. http://blog.csdn.net/stevendbaguo/article/details/22791777;
转载请注明出处:容休博客 » MySQL实现Oracle中的SYS_CONNECT_BY_PATH功能

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址