LightDB对Oracle connect by SQL语句的转换

东郭瀚玥

2023-12-01

LightDB对Oracle connect by SQL语句的转换

connect by是Oracle结构化查询中用到的，其基本语法是：

select ... from tablename
start by cond1
connect by cond2 --prior
where cond3

简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段(如emp表中的empno和mgr字段):empno, mgr那么通过表示每一条记录的mgr是谁，就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中：

cond1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
cond2是连接条件，其中用prior表示上一条记录，比如connect by prior id=praentid就是说上一条记录的id是本条记录的praentid，即本记录的父亲是上一条记录。
cond3是过滤条件，用于对返回的所有记录进行过滤。

prior和start with关键字是可选项：
prior运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，prior运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。

start with子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

我们来实际操作一下
首先先建立一张表：

CREATE TABLE emp_ 
(
    empno NUMERIC(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename VARCHAR(10),
    job VARCHAR(9),
    mgr NUMERIC(4),
    hiredate DATE,
    sal NUMERIC(7,2),
    comm NUMERIC(7,2),
    deptno NUMERIC(2)
);

再插入一些数据

INSERT INTO emp_ VALUES (7369,'SMITH','CLERK',7902,to_date('2022-01-05','yyyy-mm-dd'),800,NULL,20);
INSERT INTO emp_ VALUES (7499,'ALLEN','SALESMAN',7698,to_date('2022-01-06','yyyy-mm-dd'),1600,300,30);
INSERT INTO emp_ VALUES (7450,'ALLEN2','SALESMAN2',7698,to_date('2022-01-06','yyyy-mm-dd'),1600,300,30);
INSERT INTO emp_ VALUES (7451,'ALLEN3','SALESMAN3',7698,to_date('2022-01-06','yyyy-mm-dd'),1600,300,30);
INSERT INTO emp_ VALUES (7521,'WARD','SALESMAN',7698,to_date('2022-01-08','yyyy-mm-dd'),1250,500,30);
INSERT INTO emp_ VALUES (7566,'JONES','MANAGER',7839,to_date('2022-01-09','yyyy-mm-dd'),2975,NULL,20);
INSERT INTO emp_ VALUES (7654,'MARTIN','SALESMAN',7698,to_date('2022-01-10','yyyy-mm-dd'),1250,1400,30);
INSERT INTO emp_ VALUES (7698,'BLAKE','MANAGER',7839,to_date('2022-01-11','yyyy-mm-dd'),2850,NULL,30);
INSERT INTO emp_ VALUES (7782,'CLARK','MANAGER',7839,to_date('2022-01-12','yyyy-mm-dd'),2450,NULL,10);
INSERT INTO emp_ VALUES (7788,'SCOTT','ANALYST',7566,to_date('2022-01-13','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp_ VALUES (7839,'KING','PRESIDENT',NULL,to_date('2022-01-14','yyyy-mm-dd'),5000,NULL,10);
INSERT INTO emp_ VALUES (7844,'TURNER','SALESMAN',7698,to_date('2022-01-15','yyyy-mm-dd'),1500,0,30);
INSERT INTO emp_ VALUES (7876,'ADAMS','CLERK',7788,to_date('2022-01-16','yyyy-mm-dd'),1100,NULL,20);
INSERT INTO emp_ VALUES (7900,'JAMES','CLERK',7698,to_date('2022-01-17','yyyy-mm-dd'),950,NULL,30);
INSERT INTO emp_ VALUES (7902,'FORD','ANALYST',7566,to_date('2022-01-18','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp_ VALUES (7934,'MILLER','CLERK',7782,to_date('2022-01-19','yyyy-mm-dd'),1300,NULL,10);
INSERT INTO emp_ VALUES (7935,'MILLER2','CLERK2',7499,to_date('2022-01-20','yyyy-mm-dd'),1300,NULL,10);
INSERT INTO emp_ VALUES (7936,'MILLER3','CLERK3',7499,to_date('2022-01-21','yyyy-mm-dd'),1300,NULL,20);
INSERT INTO emp_ VALUES (7937,'MILLER4','CLERK4',7521,to_date('2022-01-22','yyyy-mm-dd'),1400,NULL,30);
INSERT INTO emp_ VALUES (7938,'MILLER5','CLERK5',7521,to_date('2022-01-23','yyyy-mm-dd'),1500,NULL,20);
INSERT INTO emp_ VALUES (7939,'MILLER6','CLERK6',7938,to_date('2022-01-24','yyyy-mm-dd'),1600,NULL,30);
INSERT INTO emp_ VALUES (7940,'MILLER7','CLERK7',7938,to_date('2022-01-25','yyyy-mm-dd'),1700,NULL,10);

在Oracle下执行connect by语句

SELECT
	LEVEL,
	empno,
	ename,
	mgr,
	sal
FROM
	emp_
CONNECT BY
	PRIOR empno = mgr
START WITH
	ename = 'BLAKE';

结果如下：
1 7698 BLAKE 7839 2850
2 7450 ALLEN2 7698 1600
2 7451 ALLEN3 7698 1600
2 7499 ALLEN 7698 1600
3 7935 MILLER2 7499 1300
3 7936 MILLER3 7499 1300
2 7521 WARD 7698 1250
3 7937 MILLER4 7521 1300
3 7938 MILLER5 7521 1300
4 7939 MILLER6 7938 1300
4 7940 MILLER7 7938 1300
2 7654 MARTIN 7698 1250
2 7844 TURNER 7698 1500
2 7900 JAMES 7698 950
我们会发现Oracle树结构的遍历顺序是先序遍历。

在LightDB下可以用WITH RECURSIVE语句可以兼容Oracle的connect by

lightdb@lt_test=# WITH RECURSIVE ref (level, empno, ename, mgr, sal) AS
lightdb@lt_test-# (
lightdb@lt_test(#     SELECT 1 AS level, empno, ename, mgr, sal
lightdb@lt_test(#     FROM emp_
lightdb@lt_test(#     WHERE ename = 'BLAKE'
lightdb@lt_test(#     UNION ALL
lightdb@lt_test(#     SELECT ref.level+1, emp_.empno, emp_.ename, emp_.mgr, emp_.sal
lightdb@lt_test(#     FROM emp_, ref
lightdb@lt_test(#     WHERE ref.empno = emp_.mgr
lightdb@lt_test(# )
lightdb@lt_test-# SELECT * FROM ref;
 level | empno |  ename  | mgr  |   sal   
-------+-------+---------+------+---------
     1 |  7698 | BLAKE   | 7839 | 2850.00
     2 |  7499 | ALLEN   | 7698 | 1600.00
     2 |  7450 | ALLEN2  | 7698 | 1600.00
     2 |  7451 | ALLEN3  | 7698 | 1600.00
     2 |  7521 | WARD    | 7698 | 1250.00
     2 |  7654 | MARTIN  | 7698 | 1250.00
     2 |  7844 | TURNER  | 7698 | 1500.00
     2 |  7900 | JAMES   | 7698 |  950.00
     3 |  7935 | MILLER2 | 7499 | 1300.00
     3 |  7936 | MILLER3 | 7499 | 1300.00
     3 |  7937 | MILLER4 | 7521 | 1300.00
     3 |  7938 | MILLER5 | 7521 | 1300.00
     4 |  7939 | MILLER6 | 7938 | 1300.00
     4 |  7940 | MILLER7 | 7938 | 1300.00
(14 rows)

lightdb@lt_test=#

从结果上看数据是一致的，但是结果顺序不一样，LightDB树结果顺序是层次遍历，原因是现在的LightDB22.3版本用的是PostgresSQL13版本的内核，PostgresSQL14版本已经改为先序遍历，在后续的LightDB更新当然会支持这一特性。

在即将发布的LightDB22.4版本中，原生支持Oracle的connect by SQL语句，敬请期待。

详细语法可参考LightDB官网查看：https://www.hs.net/lightdb
更多请登录LightDB官网进行查看：https://www.hs.net/lightdb

LightDB对Oracle connect by SQL语句的转换

LightDB对Oracle connect by SQL语句的转换

相关阅读

相关文章

相关问答

相关文档