1 概述

关于层次结构的数据集合，在日常生活中的很多地方都会碰到。比如家族关系、组织管理、行政区划、各类应用菜单中都有广泛运用。本文将通过全国行政区划应用中的一些常见用法举例对DMSQL的层次查询进行介绍。

2 实现方式

实现层次查询有两种方式，一种是类Oracle的connect by子句，另一种被大部分高版本主流关系数据库支持的CTE方式（MySQL8.0 WITH RECURSIVE AS）。本文重点讲解第一种方式。

2.1 方法1：conncet by

-- 其中CONNECT BY和 START WITH子句的位置可交换

SELECT XXX FROM TABLE CONNECT BY [NOCYCLE] 连接字段1=连接字段2 START WITH [布尔表达式]

2.2 方法2：with as

-- 【RESULT_TAB字段名列表】要和内部union all内嵌视图返回的列兼容匹配

WITH RESULT_TAB(字段名列表) AS

        (

                select i.字段名列表 FROM TABLE_NAME i [WHERE 条件]  -- 初始结果行

                union all

                select n.字段名列表 FROM TABLE_NAME n join RESULT_TAB r on n.字段名=r.字段名

        )

SELECT * FROM RESULT_TAB

3 方法1：connect by

-- 行政区划表结构

CREATE TABLE "EMP"."DICT_AREA"

(

    "ID" BIGINT IDENTITY(1,1),

    "NAME" VARCHAR(100) NOT NULL,

    "CODE" VARCHAR(50) NOT NULL,

    "PARENT_CODE" VARCHAR(50),

    PRIMARY KEY(ID)

) ;

COMMENT ON COLUMN "TEST"."DICT_AREA"."CODE" IS '区域编码';

COMMENT ON COLUMN "TEST"."DICT_AREA"."NAME" IS '区域名称';

COMMENT ON COLUMN "TEST"."DICT_AREA"."PARENT_CODE" IS '父区域编码';

3.1 查询某省份所有区域

-- 通过CONNECT_BY_ROOT函数获取当前行归属根节点的列值

WITH VW_AREA AS

         (SELECT CONNECT_BY_ROOT(NAME) AS ROOT_NAME, *

          FROM TEST.DICT_AREA

          CONNECT BY PRIOR CODE = PARENT_CODE

          START WITH PARENT_CODE = 0)

SELECT *

FROM VW_AREA

WHERE ROOT_NAME = '重庆市';

3.2 查询行政区上下级全路径

-- 通过SYS_CONNECT_BY_PATH函数获取当前行从根节点到当前节点所经过的所有节点

WITH VW_AREA AS

         (SELECT CONNECT_BY_ROOT (NAME)         AS ROOT_NAME,

                 SYS_CONNECT_BY_PATH(NAME, '/') AS ROOT_PATH,

                 *

          FROM TEST.DICT_AREA

          CONNECT BY PRIOR CODE = PARENT_CODE

          START WITH PARENT_CODE = 0)

SELECT *

FROM VW_AREA;

3.3 返回所有区域的层次级别

-- 通过伪列LEVEL返回当前层级的级别

WITH VW_AREA AS

         (SELECT CONNECT_BY_ROOT (NAME)         AS ROOT_NAME,

                 SYS_CONNECT_BY_PATH(NAME, '/') AS ROOT_PATH,

                 LEVEL                          AS AREA_LEVEL,

                 *

          FROM TEST.DICT_AREA

          CONNECT BY PRIOR CODE = PARENT_CODE

          START WITH PARENT_CODE = 0)

SELECT *

FROM VW_AREA;

3.4 返回区域是否为基层

-- 通过伪列CONNECT_BY_ISLEAF返回当前层级是否为叶子节点

WITH VW_AREA AS

         (SELECT CONNECT_BY_ROOT (NAME)         AS ROOT_NAME,

                 SYS_CONNECT_BY_PATH(NAME, '/') AS ROOT_PATH,

                 LEVEL                          AS AREA_LEVEL,

                 CONNECT_BY_ISLEAF              AS IS_LEAF,

                 *

          FROM TEST.DICT_AREA

          CONNECT BY PRIOR CODE = PARENT_CODE

          START WITH PARENT_CODE = 0)

SELECT *

FROM VW_AREA;

3.5 跳过直辖市中省份节点遍历

-- 通过START WITH子句从指定节点开始遍历（对比3.1图可知，ROOT_PATH少了一级）

WITH VW_AREA AS

         (SELECT CONNECT_BY_ROOT (NAME)         AS ROOT_NAME,

                 SYS_CONNECT_BY_PATH(NAME, '/') AS ROOT_PATH,

                 LEVEL                          AS AREA_LEVEL,

                 CONNECT_BY_ISLEAF              AS IS_LEAF,

                 REPEAT('...', LEVEL) || NAME   AS TREE_PATH,

                 *

          FROM TEST.DICT_AREA

          CONNECT BY PRIOR CODE = PARENT_CODE

          START WITH CODE = 5001)

SELECT *

FROM VW_AREA;

3.6 同级区域按名称先后排序

-- 通过ORDER SIBLINGS BY子句，将相同级别的节点按指定字段进行排序

SELECT CONNECT_BY_ROOT (NAME)         AS ROOT_NAME,

                 SYS_CONNECT_BY_PATH(NAME, '/') AS ROOT_PATH,

                 LEVEL                          AS AREA_LEVEL,

                 CONNECT_BY_ISLEAF              AS IS_LEAF,

                 REPEAT('...', LEVEL) || NAME   AS TREE_PATH,

                 *

          FROM TEST.DICT_AREA

          START WITH CODE = 42

          CONNECT BY PRIOR CODE = PARENT_CODE

          ORDER SIBLINGS BY NAME;

3.7 查询某区域的上级区域

-- 通过PRIOR操作符标示上级列（驱动表的关联列），关联返回被驱动表（connect by后无prior标示的列）

WITH VW_AREA AS

         (SELECT CONNECT_BY_ROOT (NAME)         AS ROOT_NAME,

                 SYS_CONNECT_BY_PATH(NAME, '->') AS ROOT_PATH,

                 LEVEL                          AS AREA_LEVEL,

                 CONNECT_BY_ISLEAF              AS IS_LEAF,

                 REPEAT('...', LEVEL) || NAME   AS TREE_PATH,

                 *

          FROM TEST.DICT_AREA

          CONNECT BY CODE = PRIOR PARENT_CODE

          START WITH NAME = '新洲区')

SELECT *

FROM VW_AREA;

3.8 动态生成临时结果集

在很多统计场景下，我们都需要为LEFT JOIN生成一个完整的左侧参照表。

比如，统计生成12个月的销售量，但实际情况可能是某些月份并没有任何销售记录。再比如，我们想要查询今年哪些天没有系统日志（由此判断系统是否运行）等等。

-- 利用CONNECT BY level <= 12动态生成12行月份记录（2022年只有1/2月有销售）

WITH dim_year_month AS (

       SELECT concat('2022', '-', lpad(level, 2, 0)) AS year_month

       FROM dual

       CONNECT BY level <= 12

    ),SALES AS(SELECT '2022-01' AS year_month,21 as sales_num UNION ALL SELECT '2022-02' AS year_month,13 as sales_num)

SELECT d.year_month,ifnull(s.sales_num,0) as sales_num

FROM dim_year_month d left join SALES s on d.year_month=s.year_month

ORDER BY d.year_month;

4 方法2：CTE

4.1 查询某省份所有区域

-- 利用i.parent_code=0条件过滤生成RESULT_AREA的循坏开始的初始记录行，初始记录行作为驱动表与父子表关联生成新的【结果集1】，新的【结果集1】再作为驱动表与父子表关联生成新的【结果集2】……

WITH RESULT_AREA(id, name, code, parent_code,lev,ROOT_PATH) AS

        (

                select i.*,1 as lev,'/'||i.name as ROOT_PATH FROM TEST.DICT_AREA i where i.parent_code=0   -- 初始结果行：相当于connect by子句中的start with功能

                union all

                select n.*,lev+1 as lev,concat(ROOT_PATH,'/',n.name) as ROOT_PATH from TEST.DICT_AREA n join RESULT_AREA r on n.parent_code = r.code -- 每次迭代生成的结果行与【父子】表关联生成新的结果行

        )

SELECT * FROM RESULT_AREA;