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

 
転載先:https://www.cnblogs.com/zengnansheng/p/10386041.html