Mysql查询所有子分组编码

业务中可能会有这样的需求，根据分组的grpcode查询所有的子分组（深入子分组）, 分组与上级分组关联字段为pgrpcode

BEGIN
	DECLARE sTemp VARCHAR (4000);
	DECLARE sTempChd VARCHAR (4000);
	-- 防御一下，防止无限死循环
	DECLARE iLevel INT;
	SET sTemp = '$';
	SET sTempChd = cast(groupCode AS CHAR);
	-- 防止拼装字符串溢出报错，强制截取
	SET SESSION group_concat_max_len = 2048;
	outer_label :
	BEGIN
		WHILE sTempChd IS NOT NULL DO
			SET iLevel = iLevel + 1;
			IF (iLevel > 10) THEN
				LEAVE outer_label;
			END IF;
			SET sTemp = CONCAT(sTemp, ',', sTempChd);
			SELECT
				group_concat(grpcode) INTO sTempChd
			FROM
				groupTable
			WHERE
				FIND_IN_SET(pgrpcode, sTempChd) > 0;
		END WHILE;
	END outer_label;
	RETURN sTemp;
END