oracle中查询替换,oracle中的层级查询用mysql替换

原始的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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://hatch.blog.51cto.com/9349645/1688825