Oracle 操作(select…start with…connect by…prior)

结构表如下

create table regions(

    region_id number(8) primary key,

    p_region_id number(10),

    region_path varchar2(255),

    region_grade number(8),

    local_name varchar2(100),

    zipcode varchar2(20),

    cod varchar2(4)

);

查找一个节点下面的所有节点，其中region_id是传入的参数

SELECT * FROM regions START WITH region_id = 1 connect BY prior region_id = p_region_id;

查询结果如下

MySQL 操作

创建表

DROP TABLE IF EXISTS `es_regions`;

CREATE TABLE `regions` (

  `region_id` int(10) NOT NULL AUTO_INCREMENT,

  `p_region_id` int(10) DEFAULT NULL,

  `region_path` varchar(255) DEFAULT NULL,

  `region_grade` int(8) DEFAULT NULL,

  `local_name` varchar(100) NOT NULL,

  `zipcode` varchar(20) DEFAULT NULL,

  `cod` varchar(4) DEFAULT NULL,

  PRIMARY KEY (`region_id`)

) ENGINE=InnoDB AUTO_INCREMENT=489 DEFAULT CHARSET=utf8;

利用find_in_set()函数和group_concat()函数实现递归查询：

DROP FUNCTION IF EXISTS querychild;

SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $$

CREATE FUNCTION querychild(param INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE temp VARCHAR(4000);

DECLARE sname VARCHAR(4000);

SET temp = '$';

SET sname = CAST(param AS CHAR);

WHILE sname IS NOT NULL DO

SET temp = CONCAT(temp,',',sname);

SELECT GROUP_CONCAT(region_id) INTO sname FROM regions WHERE FIND_IN_SET(p_region_id,sname)>0;

END WHILE;

RETURN temp;

END $$

调用方式

SELECT * FROM regions WHERE FIND_IN_SET(region_id, querychild(1));

查询结果如下：