SQL中with recursive XXX as

 

首先有上述这个城市级别组织结构，根据这个结构定义表和插入数据。

create table city_info(
	id int primary key auto_increment,
	name varchar(52),
	p_id int
);

insert into city_info(id,name,p_id) values
(1,'中国',-1),(2,'江苏省',1)
,(3,'南京市',2),(4,'六合区',3),(5,'高淳区',3)
,(6,'溧水区',3),(7,'江宁区',3),(8,'徐州市',2);

表数据如下，

with recursive XXX as 这是一个用来执行递归的SQL查询，他会把查询出来的结果再次代入到查询子句中继续查询。

如下模板所示，as里面是一个子查询，子查询不断递归返回数据，然后数据统一由select id from alias_table_name返回。

SELECT id,name,p_id FROM `city_info` where id in (
	with recursive alias_table_name as (子查询)
	select id from alias_table_name
)

子查询里面是这样的:
    初始条件；
    union all
    循环查询条件；

现在定一个目标，查询南京市，及所有他的附属区域（即子节点）。

所以定义初始条件就是查询南京市，

初始条件： select a.id from city_info a where a.id = 3

循环条件就是 p_id =  id, 这种，

循环查询条件：select b.id from city_info b,alias_table_name 
where b.p_id = alias_table_name.id

所以上述用with recursive XXX as 这个语句组合起来就是，

SELECT id,name,p_id FROM  city_info  where id in (
	with recursive alias_table_name as (
			select a.id from city_info a where a.id = 3
			union all
			select b.id from city_info b,alias_table_name where b.p_id = alias_table_name.id
	)
	select id from alias_table_name
)

执行结果如下，

 分析递归行为，

第一次执行递归： id=3的数据，赋值给表alias_table_name
第二次执行递归： b.p_id = 3的数据，赋值给表alias_table_name
第三次执行递归： b.p_id = 4,5,6,7的数据，此时为空，返回递归

这样就查询出 id=3,4,5,6,7的数据。

PS: 我用MySQL5.X.X 版本是报语法错误，换成mysql.8就好了。

还有一个是with XXX as ，这个是把查询当做子查询，并有一个别名XXX，并没有递归，同时数据库会对该子句生成临时表；

如下案列所示，

SELECT id,name,p_id FROM  city_info  where id in (
	with alias_table_name as (
			select a.id from city_info a where a.id = 3
-- 			union all
-- 			select a.id from city_info a where a.id = 4
	)
	select id from alias_table_name
)

SELECT id,name,p_id FROM  city_info  where id in (
	with alias_table_name as (
			select a.id from city_info a where a.id = 3
 			union all
 			select a.id from city_info a where a.id = 4
	)
	select id from alias_table_name
)