RECURSIVE - 递归查询

实际中往往需要涉及到查询一张表中存在上下级关系的数据。

这样的情景有很多，比如岗位配置表，存在多级部门，部门间存在上下级关系；比如业务字典表，存在多级字典，其中也存在上下级关系。

当层级只有一两层的时候，使用关联查询、子查询或者其它任何方式，都能够比较容易完成查询功能。
例如，当存在一个岗位部门表 POST，其中内容结构如下：

POST_CODE	POST_NAME	POST_PRE_CODE
president	总裁办公室
business	业务部	president
technical	技术部	president
logistics	后勤部	president
humanResource	人力资源部	president
account	财务部	president
security	安保部	president
culture	企业文化部	president

对这样的结构进行查询，并不困难。如下：

SELECT P.POST_CODE,
       CASE
           WHEN P.POST_PRE_CODE IS NULL THEN P.POST_NAME
           ELSE CONCAT_WS(' - ', (SELECT O.POST_NAME FROM POST O WHERE O.POST_CODE = P.POST_PRE_CODE),
                       P.POST_NAME) END AS POST_NAME
FROM POST P ORDER BY POST_PRE_CODE;

得到的结果：

POST_CODE	POST_NAME
president	总裁办公室
business	总裁办公室 - 业务部
technical	总裁办公室 - 技术部
logistics	总裁办公室 - 后勤部
humanResource	总裁办公室 - 人力资源部
account	总裁办公室 - 财务部
security	总裁办公室 - 安保部
culture	总裁办公室 - 企业文化部

但当层级越来越多之后，查询会变得麻烦起来。

例如，当“技术部”存在下属岗位如“研发组”、“美工组”、“测试组”、“运维组”……再当“研发组”又存在下属岗位“软件开发”、“硬件开发”、“大数据开发”……
可想而知，通过上述的查询SQL已经无法轻易完成这样的查询需求。

在这种情况下，使用 RECURSIVE ，进行递归查询是非常适合的。
再举个例子，存在地区区域表 AREA ，最高层级是省，最低层级到某个县/区的某个地点，至少存在四级结构。

此时想要获取某指定省份，如“湖南省”，在表中全部下属的地市、区县及地点，并让每个地点都附带上它所有的上级结构，该如何通过SQL完成？
使用 RECURSIVE 进行递归查询，会让事情变得简单。如下：

WITH RECURSIVE city AS
(
    SELECT AREA_CODE,
           AREA_NAME
    FROM AREAS
    WHERE AREA_CODE = 'hunan'
    UNION ALL
    SELECT org.AREA_CODE,
           CONCAT_WS(' - ', city.AREA_NAME, org.AREA_NAME) AREA_NAME
    FROM city
             JOIN AREAS org ON org.AREA_PID = city.AREA_CODE
)
SELECT AREA_CODE,
       AREA_NAME
FROM city;

最终，我们可以得到下面的结构

POST_CODE	POST_NAME
hunan	湖南省
changsha	湖南省 - 长沙市
yueyang	湖南省 - 岳阳市
hengyang	湖南省 - 衡阳市
xiangtan	湖南省 - 湘潭市
furongqu	湖南省 - 长沙市 - 芙蓉区
tianxinqu	湖南省 - 长沙市 - 天心区
yueluqu	湖南省 - 长沙市 - 岳麓区
kaifuqu	湖南省 - 长沙市 - 开福区
yuhuaqu	湖南省 - 长沙市 - 雨花区
wangchengqu	湖南省 - 长沙市 - 望城区
liuyangshi	湖南省 - 长沙市 - 浏阳市
ningxiangshi	湖南省 - 长沙市 - 宁乡市
changshaxian	湖南省 - 长沙市 - 长沙县
junshanqu	湖南省 - 岳阳市 - 君山区
xiangyuxian	湖南省 - 长沙市 - 芙蓉区 - 湘域熙岸小区
chengshihuayuan	湖南省 - 长沙市 - 岳麓区 - 万科城市花园
fangjushoufu	湖南省 - 岳阳市 - 君山区 - 房聚首府小区
junshanyihao	湖南省 - 岳阳市 - 君山区 - 君山壹号

成功得到满足需求的查询结果。
WITH语句本身能够将复杂语句拆分为多个简单语句，在进行复杂的查询时尤其适用。
而 RECURSIVE 关键字则是起到了引入自身的作用，从而实现了递归查询。

不过需要注意的是，RECURSIVE 仅在MySQL8.0以上的版本才能使用哦