mysql set stemp = $_Mysql之子父级查询

函数递归方式

根据父id获取所有子类ids(包含当前父id) 可以传递多个id 逗号分隔

set global log_bin_trust_function_creators=TRUE;

DROP FUNCTION IF EXISTS getProductCategoryChildIDs;

CREATE FUNCTION getProductCategoryChildIDs(categoryId varchar(100)) RETURNS varchar(1000)

BEGIN

DECLARE sTemp VARCHAR (1000);

DECLARE sTempIds VARCHAR (1000);

SET sTemp = '$';

SET sTempIds = CAST(categoryId AS char(10));

WHILE sTempIds IS NOT NULL DO

SET sTemp = CONCAT(sTemp, ',', sTempIds);

SELECT GROUP_CONCAT(id) INTO sTempIds FROM t_product_category WHERE FIND_IN_SET(categoryId, sTempIds) > 0;

END WHILE;

RETURN sTemp;

END

#SELECT getProductCategoryChildIDs('1');

#SELECT getProductCategoryChildIDs('1,2');

根据子类id获取所有父类ids (包含当前子类id)

CREATE FUNCTION getParentIDs(childId INT) RETURNS VARCHAR(1000) CHARSET utf8

BEGIN

DECLARE sTemp VARCHAR (1000);

DECLARE sTempIds VARCHAR (1000);

SET sTemp = '$';

SET sTempIds = CAST(childId AS CHAR(10));

WHILE sTempIds IS NOT NULL DO

SET sTemp = CONCAT(sTemp, ',', sTempIds);

SELECT GROUP_CONCAT(parent_id) INTO sTempIds FROM category WHERE FIND_IN_SET(id, sTempIds) > 0;

END WHILE;

RETURN sTemp;

END

#SELECT getParentIDs(10);

sql循环方式

假设表结构为id,name,parent_id

查询id为5的所有父id

SELECT

T2.id,

T2.`name`

FROM

(

SELECT

@r AS _id,

(

SELECT

@r := parent_id

FROM

category

WHERE

id = _id

) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := 5, @l := 0) vars,

category h

WHERE

@r <> 0

) T1

JOIN category T2 ON T1._id = T2.id

ORDER BY

T1.lvl DESC

标签：SET,sTempIds,父级,查询,sTemp,Mysql,id,SELECT,1000

来源： https://www.cnblogs.com/zengnansheng/p/10386041.html