MySQL 获取某一个分类ID的所有父或子分类查询结果

创建一个自定义函数：

set global log_bin_trust_function_creators=1;
 1 DELIMITER $$
 2 
 3 USE `dressv_website`$$
 4 
 5 DROP FUNCTION IF EXISTS `fn_QueryRelation`$$
 6 
 7 CREATE DEFINER=`sa`@`%` FUNCTION `fn_QueryRelation`(AreaId INT,Relation TINYINT(1)) RETURNS VARCHAR(4000) CHARSET utf8
 8 BEGIN
 9 DECLARE sTemp VARCHAR(4000);
10 DECLARE sTempChd VARCHAR(4000);
11 SET sTemp = '$';
12 SET sTempChd = CAST(AreaId AS CHAR);
13 IF Relation=0 THEN 
14 WHILE sTempChd IS NOT NULL DO
15 SET sTemp = CONCAT(sTemp,',',sTempChd);
16 SELECT GROUP_CONCAT(parentId) INTO sTempChd FROM product_leimu WHERE FIND_IN_SET(id,sTempChd)>0;
17 END WHILE;
18 ELSE
19 WHILE sTempChd IS NOT NULL DO
20 SET sTemp = CONCAT(sTemp,',',sTempChd);
21 SELECT GROUP_CONCAT(id) INTO sTempChd FROM product_leimu WHERE FIND_IN_SET(parentId,sTempChd)>0;
22 END WHILE;
23 END IF;
24 RETURN sTemp;
25     END$$
26 
27 DELIMITER ;
fn_QueryRelation

查询语句：

1 SELECT * FROM product_leimu WHERE FIND_IN_SET(id, fn_QueryRelation(11,0));
MYSQL

 

posted @ 2015-01-05 18:49  —八戒—  阅读(4468)  评论(0)  编辑  收藏  举报