递归查询语法

Vastbase在Oracle兼容模式下支持使用START WITH... CONNECT BY子句实现递归查询，这通常用于处理具有层次或树状结构的数据。

• CONNECT BY用于定义层次查询的条件。

  • 支持多表连接查询。

  • 支持在CONNECT BY子句中指定PRIOR关键字修饰递归键，表示以此列为递归键进行递归。当前支持对表中的列、表达式指定PRIOR关键字。

• 递归查询中的伪列可以用来表示当前行在层次结构中的级别，包括如下选项：

  • connect_by_isleaf选项，用于指示当前行是否为树的叶子节点。

  • connect_by_iscycle选项，用于指示当前行是否为循环行。

  • connect_by_root选项，可以根据当前行返回被视为根节点的行的列值。

• 支持排序子句order siblings by选项，可对结果集进行排序，用于层次查询connect by中。

语法格式

SELECT ... FROM table
START WITH condition1
CONNECT BY condition

参数说明

• table

  查询表名。

• condition1

  表示递归的初始条件。若省略该子句，单独使用CONNECT BY子句，则表示以表中的所有行作为初始集合。

• condition

  任何条件语句。condition的格式必须为：

  [PRIOR column1 = column2 | column1 = prior column2]
  [PRIOR table_name.column_name=table_name.column_name | table_name.column_name = PRIOR table_name.column_name ]
  [PRIOR expr1 = expr2 |  expr1 = PRIOR expr2]
  

  其中指定PRIOR关键字修饰的列代表以这列为递归键进行递归。当前约束支持对表中的列、对表达式指定PRIOR关键字。

注意事项

• 仅在数据库兼容模式为Oracle时支持对表达式指定PRIOR关键字（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。

• 不支持对子查询指定PRIOR关键字。

• 不支持在PRIOR中使用聚合函数。

示例

示例1： 单表递归查询。

1、创建测试表并插入数据。

create table employee(
emp_id number(18),
lead_id number(18),
emp_name varchar2(200),
salary number(10,2),
dept_no varchar2(8)
);

insert into employee values('1',0,'king','1000000.00','001');
insert into employee values('2',1,'jack','50500.00','002');
insert into employee values('3',1,'arise','60000.00','003');
insert into employee values('4',2,'scott','30000.00','002');
insert into employee values('5',2,'tiger','25000.00','002');
insert into employee values('6',3,'wudde','23000.00','003');
insert into employee values('7',3,'joker','21000.00','003');

2、查询以lead_id为3开始的节点的所有直属节点。

select emp_id,lead_id,emp_name
from employee
start with lead_id=3
connect by prior emp_id = lead_id;

返回结果为：

 emp_id | lead_id | emp_name
--------+---------+----------
      6 |       3 | wudde
      7 |       3 | joker
(2 rows)

3、清理环境。

DROP TABLE employee;

示例2： 多表连接查询。

1、创建测试表并插入数据。

create table t1(id int);
create table t2(id int);
declare
        i int:=0;
    begin
        for i in 1..5 loop
        insert into t1 values(i);
        insert into t2 values(i+1);
        end loop;
        commit;
    end;
        /

2、执行多表连接查询。

select t1.id as id1,t2.id as id2 
from t1,t2 
where t1.id+1=t2.id 
start with t1.id=1 
connect by prior t2.id=t1.id;

返回结果为：

 id1 | id2
-----+-----
   1 |   2
   2 |   3
   3 |   4
   4 |   5
   5 |   6
(5 rows)

3、清理环境。

DROP TABLE t1;
DROP TABLE t2;

示例3 表达式查询。

示例3仅在Oracle兼容模式下可执行。

1、创建测试表。

CREATE TABLE customer_orders_1202637 ( id INT, pid INT,name VARCHAR(20),OTHER VARCHAR(20) ) ;
insert into customer_orders_1202637 values(1, null,'Cust. Ord. XYZ','BLAKE');
insert into customer_orders_1202637 values(2, 0,'Cust. Ord. ABC','CLARK');
insert into customer_orders_1202637 values(3, 0,'Cust. Ord. DEF','CLARK');
insert into customer_orders_1202637 values(4, 1,'Cust. Ord. 123','JONES');

2、执行表达式查询。

select pid,name,level,CONNECT_BY_ISLEAF from customer_orders_1202637
start with id is not null
connect by nocycle
PRIOR length(pid)+1=id order by 1,2,3;

返回结果为：

 pid |      name      | level | connect_by_isleaf
-----+----------------+-------+-------------------
   0 | Cust. Ord. ABC |     1 |                 1
   0 | Cust. Ord. DEF |     1 |                 1
   1 | Cust. Ord. 123 |     1 |                 1
     | Cust. Ord. XYZ |     1 |                 1
(4 rows)

3、执行类型转换查询。

select pid,name,level,CONNECT_BY_ISLEAF from customer_orders_1202637
start with id is not null
connect by nocycle
PRIOR pid::varchar+1=id order by 1,2,3;

返回结果为：

 pid |      name      | level | connect_by_isleaf
-----+----------------+-------+-------------------
   0 | Cust. Ord. ABC |     1 |                 0
   0 | Cust. Ord. ABC |     2 |                 0
   0 | Cust. Ord. DEF |     1 |                 0
   1 | Cust. Ord. 123 |     1 |                 0
     | Cust. Ord. XYZ |     1 |                 1
     | Cust. Ord. XYZ |     2 |                 1
     | Cust. Ord. XYZ |     2 |                 1
     | Cust. Ord. XYZ |     3 |                 1
(8 rows)

4、清理环境。

DROP TABLE customer_orders_1202637;

connect_by_isleaf选项

功能描述

递归查询中的一个伪列，用于指示当前行是否为树的叶子节点（即没有子节点的节点），如果是返回1，否则返回0。

语法格式

SELECT ... CONNECT_BY_ISLEAF "IsLeaf",[LEVEL],
from table
start with condition1
connect by condition;

参数说明

• LEVEL

  一个伪列（可选），代表当前这个节点所在的层级，对于根节点，level返回1。

• IsLeaf

  表示当前行是否为树的叶子节点（即没有子节点的节点），如果是返回1，否则返回0。

注意事项

当未指定prior时，connect_by_isleaf始终为0。

示例

1、创建测试表并插入数据。

CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); 

2、查询当前行是否为叶子节点。

SELECT  ename, CONNECT_BY_ISLEAF "IsLeaf",LEVEL
FROM EMP
WHERE LEVEL <= 3 AND deptno= 10
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr AND LEVEL <= 4
order by ename,"IsLeaf";

返回结果为：

 ename  | IsLeaf | level
--------+--------+-------
 CLARK  |      0 |     2
 KING   |      0 |     1
 MILLER |      1 |     3
(3 rows)

3、清理环境。

DROP TABLE EMP;

connect_by_iscycle选项

功能描述

递归查询中的一个伪列，用于指示当前行是否为循环行，如果是则返回1，否则返回0。

语法格式

SELECT ... CONNECT_BY_ISCYCLE "IsCYCLE",[LEVEL],
from table
start with condition1
connect by condition;

参数说明

• LEVEL

  一个伪列（可选），代表当前这个节点所在的层级，对于根节点，level返回1。

• IsCYCLE

  表示当前行是否为循环行，如果是则返回1，否则返回0。

注意事项

当未指定prior时，connect_by_iscycle始终为0。

示例

1、创建测试表cyctest并插入测试数据。

 create table cyctest(tn varchar,fatherid number,childid number);
 insert into cyctest values('a',null,1);
 insert into cyctest values('b',1,2);
 insert into cyctest values('c',1,3);
 insert into cyctest values('d',2,4);
 insert into cyctest values('e',4,1);
 insert into cyctest values('f',4,5);

2、使用connec_by_cycle查询循环行。

select tn,fatherid,childid,connect_by_iscycle iscycle ,level from cyctest start with fatherid is null connect by nocycle prior childid=fatherid;

结果返回如下：

 tn | fatherid | childid | iscycle | level
----+----------+---------+---------+-------
 a  |          |       1 |       0 |     1
 b  |        1 |       2 |       0 |     2
 d  |        2 |       4 |       1 |     3
 f  |        4 |       5 |       0 |     4
 c  |        1 |       3 |       0 |     2
(5 rows)

3、清理环境。

DROP TABLE cyctest;

connect_by_root选项

功能描述

一个一元运算符，可用于限定列，仅在递归查询中有效，可以根据当前行返回被视为根节点的行的列值，即可以根据当前行得到其根节点的列值。

CONNECT_BY_ROOT运算符可在SELECT列表、WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句和ORDER SIBLINGS BY子句中使用，只要SELECT命令用于分层查询。不能在分层查询的CONNECT BY子句或START WITH子句中使用。可将CONNECT_BY_ROOT应用于一个涉及列的表达式，但这样做时，该表达式必须用圆括号包围。

语法格式

SELECT CONNECT_BY_ROOT cloumn [, ...]
from table
start with condition1
connect by condition

注意事项

无。

参数说明

cloumn

CONNECT_BY_ROOT子句用于显示根节点的列值，col为输出列的名称。

示例

1、创建测试表并插入数据。

CREATE TABLE family_tree_2(id int primary key,appellation varchar(30),parent_id int,age int);
insert into family_tree_2 values(1,'爷爷',0,88);
insert into family_tree_2 values(2,'大伯',1,66);
insert into family_tree_2 values(5,'堂兄',2,32);
insert into family_tree_2 values(6,'堂妹',2,24);
insert into family_tree_2 values(3,'爸爸',1,58);
insert into family_tree_2 values(7,'小明',3,26);
insert into family_tree_2 values(4,'小姑',1,52);
insert into family_tree_2 values(8,'表姐',4,28);
insert into family_tree_2 values(9,'表弟',4,20);

2、从爸爸辈开始，对兄弟子侄进行排序。

SELECT connect_by_root id root, connect_by_isleaf isleaf,id,appellation,age
FROM family_tree_2 
CONNECT BY PRIOR id= parent_id
start with parent_id = 1 
order siblings by age;

返回结果为：

 root | isleaf | id | appellation | age
------+--------+----+-------------+-----
 4    |      0 |  4 | 小姑        |  52
 4    |      1 |  9 | 表弟        |  20
 4    |      1 |  8 | 表姐        |  28
 3    |      0 |  3 | 爸爸        |  58
 3    |      1 |  7 | 小明        |  26
 2    |      0 |  2 | 大伯        |  66
 2    |      1 |  6 | 堂妹        |  24
 2    |      1 |  5 | 堂兄        |  32
(8 rows)

3、清理环境。

DROP TABLE family_tree_2;

order siblings by选项

功能描述

排序子句，仅用于层次查询connect by中，可以让兄弟节点（即同一父级的兄弟）按照指定的列进行排序（升序或者降序）。

语法格式

SELECT ...
FROM table
start with condition1
connect by condition
ORDER SIBLINGS BY expression

参数说明

expression

排序的依据。

注意事项

无。

示例

1、创建测试表并插入数据。

CREATE TABLE family_tree(id int primary key,appellation varchar(30),parent_id int,age int);
insert into family_tree values(1,'爷爷',0,88);
insert into family_tree values(2,'大伯',1,66);
insert into family_tree values(5,'堂兄',2,32);
insert into family_tree values(6,'堂妹',2,24);
insert into family_tree values(3,'爸爸',1,58);
insert into family_tree values(7,'小明',3,26);
insert into family_tree values(4,'小姑',1,52);
insert into family_tree values(8,'表姐',4,28);
insert into family_tree values(9,'表弟',4,20);

2、对相同父亲的兄弟进行排序。

SELECT * FROM family_tree 
CONNECT BY PRIOR id= parent_id
start with appellation ='爷爷' 
order siblings by age asc;

返回结果为：

 id | appellation | parent_id | age
----+-------------+-----------+-----
  1 | 爷爷        |         0 |  88
  4 | 小姑        |         1 |  52
  9 | 表弟        |         4 |  20
  8 | 表姐        |         4 |  28
  3 | 爸爸        |         1 |  58
  7 | 小明        |         3 |  26
  2 | 大伯        |         1 |  66
  6 | 堂妹        |         2 |  24
  5 | 堂兄        |         2 |  32
(9 rows)

3、清理环境。

DROP TABLE family_tree;