原始的oracle语句为
select ‘|‘||c.seq_cate||‘|‘
from osr_category c
start with c.seq_cate = #serviceCategory#
connect by prior c.seq_cate = c.parent_id)
mysql没有相应的方法实现递归查询树的功能，所以只好根据网上所说的那样写个函数来实现：
CREATE FUNCTION getChildList(rootId VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN
DECLARE pTemp VARCHAR(1000);
DECLARE cTemp VARCHAR(1000);
SET pTemp=‘$‘;
SET cTemp=rootId;
WHILE cTemp is not null DO
set pTemp=CONCAT(pTemp,‘,‘,cTemp);
SELECT GROUP_CONCAT(SEQ_CATE) INTO cTemp from osr_category
WHERE FIND_IN_SET(PARENT_ID,cTemp)>0;
END WHILE;
RETURN pTemp;
END
随后其sql语句应该改为：
select ‘|‘||c.seq_cate||‘|‘
from osr_category c
where FIND_IN_SET(c.seq_cate, getChildList(#serviceCategory#))
oracle中的层级查询用mysql替换
标签：tart with...connect by