2 u012870294 u012870294 于 2017.09.07 18:24 提问

mysql递归查询树形结构,主键ID保存在一张临时表中,哪位大神帮忙看下,急。。。。。。 20C

CREATE FUNCTION getTypeChildList (rootId BIGINT)
RETURNS @service_type TABLE (type_id VARCHAR (1000))
AS
BEGIN
DECLARE sChildList VARCHAR (1000);
DECLARE sChildTemp VARCHAR (1000);
SET sChildTemp = cast(rootId AS CHAR);
WHILE sChildTemp IS NOT NULL DO
IF (sChildList IS NOT NULL) THEN
INSERT INTO @service_type VALUES (sChildTemp);
END IF;
SELECT group_concat(type_id) INTO sChildTemp FROM service_type where FIND_IN_SET(parent_id,sChildTemp)>0;
END WHILE;
RETURN;
END;

    表:CREATE TABLE `service_type` (

type_id bigint(20) NOT NULL,
type_name varchar(100) DEFAULT NULL COMMENT '类型名称',
parent_id bigint(20) DEFAULT NULL COMMENT '上一级类型id',
PRIMARY KEY (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Csdn user default icon
上传中...
上传图片
插入图片