C'est La Vie

    人生无彩排,每一天都是现场直播!

    MySQL 查询一分类所有的子分类

    CREATE FUNCTION `getChildLst`(rootId INT) RETURNS varch […]
    CREATE FUNCTION `getChildLst`(rootId INT)
    RETURNS varchar(1000)
    BEGIN
    	DECLARE sTemp VARCHAR(1000);
    	DECLARE sTempChd VARCHAR(1000);
    	SET sTemp = '$';
    	SET sTempChd =cast(rootId as CHAR);
    	WHILE sTempChd is not null DO
    		SET sTemp = concat(sTemp,',',sTempChd);
    		SELECT group_concat(cid) INTO sTempChd FROM zz_category where FIND_IN_SET(pid,sTempChd)>0;
    	END WHILE;
    	RETURN sTemp;
    END

    有可能会遇到 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary 的问题,只需要在MySQL中执行如下命令就可以了:

    set global log_bin_trust_function_creators=TRUE;

    查询的SQL语句:

    select getChildLst(133) cids;

    结果:

    +---------------------------+
    | cids                      |
    +---------------------------+
    | $,133,137,141,142,143,144 |
    +---------------------------+

     

    发表回复

    您的电子邮箱地址不会被公开。 必填项已用*标注