Gbase8aSTART WITH...CONNECT BY语法详解

START WITH...CONNECT BY语法是 兼容Oracle 数据库中用于处理层次结构数据的语法，主要用于树形结构的查询。这种语法非常适用于组织结构、菜单系统、产品分类等具有父子关系的数据场景。

应用场景：

组织结构查询：查询公司层级关系，如部门-子部门关系

菜单系统：多级菜单的查询

产品分类：多级分类体系的查询

评论回复：主评论和回复评论的层级关系

家谱关系：家族成员之间的父子关系

语法规则：

START WITH：指定层次结构的起点（根节点）

CONNECT BY：定义父子关系。从右往左找

PRIOR：运算符，表示前一行的列值

ORDER SIBLINGS BY：对同一层级的兄弟节点进行排序

使用前提条件

分级查询 from 子句必须是表，且必须是复制表；分级查询可以作为子查询出现，但分级查询中不允许出现子查询

示例：

-- 创建员工表
CREATE TABLE employees (
   emp_id int PRIMARY KEY,
   emp_name VARCHAR(50),
   position VARCHAR(50),
   manager_id int,
   department VARCHAR(50)
)replicated;

-- 插入数据
INSERT INTO employees VALUES (1, '张总', 'CEO', NULL, '总部');
INSERT INTO employees VALUES (2, '李经理', '技术总监', 1, '技术部');
INSERT INTO employees VALUES (3, '王经理', '销售总监', 1, '销售部');
INSERT INTO employees VALUES (4, '赵主管', '开发主管', 2, '技术部');
INSERT INTO employees VALUES (5, '钱主管', '测试主管', 2, '技术部');
INSERT INTO employees VALUES (6, '孙员工', '高级开发', 4, '技术部');
INSERT INTO employees VALUES (7, '周员工', '初级开发', 4, '技术部');
INSERT INTO employees VALUES (8, '吴员工', '测试工程师', 5, '技术部');
INSERT INTO employees VALUES (9, '郑销售', '大区经理', 3, '销售部');
INSERT INTO employees VALUES (10, '冯销售', '区域经理', 9, '销售部');

-- 创建产品分类表
CREATE TABLE product_categories (
   category_id int PRIMARY KEY,
   category_name VARCHAR(50),
   parent_id int
)replicated;

-- 插入数据
INSERT INTO product_categories VALUES (1, '电子产品', NULL);
INSERT INTO product_categories VALUES (2, '服装', NULL);
INSERT INTO product_categories VALUES (3, '手机', 1);
INSERT INTO product_categories VALUES (4, '笔记本电脑', 1);
INSERT INTO product_categories VALUES (5, '男装', 2);
INSERT INTO product_categories VALUES (6, '女装', 2);
INSERT INTO product_categories VALUES (7, '智能手机', 3);
INSERT INTO product_categories VALUES (8, '功能手机', 3);
INSERT INTO product_categories VALUES (9, '游戏本', 4);
INSERT INTO product_categories VALUES (10, '商务本', 4);
INSERT INTO product_categories VALUES (11, 'T恤', 5);
INSERT INTO product_categories VALUES (12, '牛仔裤', 5);

查询示例：

查询整个组织结构（自上而下）

SELECT    emp_name,    position,    LEVEL as hierarchy_level

FROM employees

START WITH emp_id = 2  -- 从李经理(技术总监)开始

CONNECT BY PRIOR emp_id = manager_id;  --从右往左找，寻找manager_id=emp_id的行，找李经理的下属。

查询整个组织结构（自底向上）

SELECT    emp_name,    position,    LEVEL as hierarchy_level

FROM employees

START WITH emp_id = 2  -- 从李经理(技术总监)开始

CONNECT BY PRIOR manager_id= emp_id;   --从右往左找，寻找emp_id=manager_id的行，找李经理的上级。

查询所有电子产品

SELECT category_id,category_name,level

from product_categories 

START WITH category_id=1

CONNECT BY PRIOR category_id=parent_id;   -- 从右往左找，寻找parent_id=category_id的行，找电子产品

使用ORDER SIBLINGS BY对同级排序

SELECT category_id,category_name,level as a

from product_categories 

START WITH category_id=1

CONNECT BY PRIOR category_id=parent_id

ORDER SIBLINGS BY  category_id;

使用CONNECT_BY_ROOT获取根节点信息

SELECT category_id,category_name,level as a, CONNECT_BY_ROOT category_name AS type

from product_categories 

START WITH category_id=1

CONNECT BY PRIOR category_id=parent_id

ORDER SIBLINGS BY  category_id;

CONNECT BY与START WITH位置可以互换

SELECT category_id,category_name,level as a, CONNECT_BY_ROOT category_name AS type

from product_categories 

CONNECT BY PRIOR category_id=parent_id

START WITH category_id=1

ORDER SIBLINGS BY  category_id;

检测到存在cycle时会自动报错退出：

ERROR 1708 (HY000): [172.16.3.246:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01EX-700) Gbase general error: Cycle exists in connect by clause
 

可以通过CONNECT BY NOCYCLE PRIOR指定，会在检测到循环时停止向下遍历，结果集中不会显示循环产生的重复记录。

复制表中，存在delete数据时会报错推出：

DETAIL: (GBA-01EX-700) Gbase general error: Restrict: Connect by clause must be used with table not deleted

可以开启_gbase_connect_by_support_table_with_deleted_records参数或是使用子查询解决。