Shulou(Shulou.com)06/01 Report--

Oracle's Start with...Connect By implements the recursive query of the tree, but now it is required to use MYSQL to implement the same recursive query tree. This function is something I have never used before, so I searched the Internet and found some information and began to do it.

The original oracle statement is

Select'|'| | c.seq_cate | |'|'

From osr_category c

Start with c.seq_cate = # serviceCategory#

Connect by prior c.seq_cate = c.parent_id)

Mysql has no corresponding method to implement the function of recursive query tree, so we have to write a function to implement it according to what is said on the Internet:

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

Then its sql statement should be changed to:

Select'|'| | c.seq_cate | |'|'

From osr_category c

Where FIND_IN_SET (c.seq_cate, getChildList (# serviceCategory#))

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.