logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

递归查询语法

功能描述

Vastbase G100兼容Oracle递归查询START WITH...CONNECT BY...语法，并且CONNECT BY支持多表连接查询。

语法格式

START WITH condition1 CONNECT BY condition
CONNECT BY condition [ START WITH condition1 ]

参数说明

  • condition1

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

  • condition

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

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

    其中PRIOR表示对列指定PRIOR关键字代表以这列为递归键进行递归。当前约束只能对表中的列指定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、查询以emp_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)

示例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)

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 deptno = mgr AND LEVEL <= 4
order by ename,"IsLeaf";

返回结果为：

 ename | IsLeaf | level
-------+--------+-------
 KING  |      1 |     1
(1 row)

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)

order siblings by选项

功能描述

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

语法格式

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

参数说明

  • table

    表名。

  • 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)

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为输出列的名称。

  • table

    表名。

注意事项

无。

示例

本示例测试表使用order siblings by选项示例中所创建的表。

对同已父级的兄弟进行排序。

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

返回结果为：

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