lightdb支持distinct ... connect by的使用
在LightDB 23.3版本中，支持DISTINCT 与CONNECT BY联合使用（具体connect by使用可参考文章：https://blog.csdn.net/s_lisheng/article/details/128331881，https://blog.csdn.net/qq_22066003/article/details/128339067）
使用 DISTINCT 和 CONNECT BY 可以实现一些特定的查询操作。
DISTINCT 用于返回唯一不同的值。它可以用于单个列或多个列，以消除结果集中的重复值。例如，以下查询将返回一个列表中不同的城市名称：
CONNECT BY 用于在查询中生成层次结构。它基于表中的父子关系列（通常是自引用的外键列），递归地连接行，以构建树状结构。以下是一个示例查询，假设有一个名为 "employees" 的表，其中包含员工的 ID、姓名和上级的 ID：
上述查询将从具有 ID 为 1 的员工开始，逐级向上连接上级，直到达到树的根节点。LEVEL 列表示每个员工在层次结构中的级别。
在LightDB中两者具体使用举例如下：
create table test_area(id int, name text, pid int);
insert into test_area values(1, 'china', 0);
insert into test_area values(101, 'zhejiang', 1);
insert into test_area values(102, 'jiangsu', 1);
insert into test_area values(103, 'heilongjiang', 1);
insert into test_area values(10101, 'hangzhou', 101);
insert into test_area values(10102, 'ningbo', 101);
insert into test_area values(10103, 'shaoxing', 101);
insert into test_area values(10104, 'wenzhou', 101);
insert into test_area values(1010101, 'binjiang', 10101);
insert into test_area values(1010102, 'shangcheng', 10101);
insert into test_area values(1010103, 'xihu', 10101);
insert into test_area values(1010101, 'gongshu', 10101);
create view v_testarea as select * from test_area;
select id, name, pid from v_testarea start with name = 'hangzhou' connect by pid = prior id;
id | name | pid
---------+------------+-------
10101 | hangzhou | 101
1010101 | binjiang | 10101
1010102 | shangcheng | 10101
1010103 | xihu | 10101
1010101 | gongshu | 10101
(5 rows)
drop view v_testarea;
drop table test_area;
-- only support one simple table, not suport subquery, join...
create table test_area1(id int, name text, pid int);
insert into test_area1 values(1, 'china', 0);
insert into test_area1 values(101, 'zhejiang', 1);
insert into test_area1 values(102, 'jiangsu', 1);
insert into test_area1 values(103, 'heilongjiang', 1);
insert into test_area1 values(10101, 'hangzhou', 101);
insert into test_area1 values(10102, 'ningbo', 101);
insert into test_area1 values(10103, 'shaoxing', 101);
insert into test_area1 values(10104, 'wenzhou', 101);
insert into test_area1 values(1010101, 'binjiang', 10101);
insert into test_area1 values(1010102, 'shangcheng', 10101);
insert into test_area1 values(1010103, 'xihu', 10101);
insert into test_area1 values(1010101, 'gongshu', 10101);
select id, name, pid from (select * from test_area1) start with name = 'hangzhou' connect by id = pid;
id | name | pid
-------+----------+-----
10101 | hangzhou | 101
(1 row)
create table test_area2(id int, name text, pid int);
insert into test_area2 values(1, 'china', 0);
insert into test_area2 values(101, 'zhejiang', 1);
insert into test_area2 values(102, 'jiangsu', 1);
insert into test_area2 values(103, 'heilongjiang', 1);
insert into test_area2 values(10101, 'hangzhou', 101);
insert into test_area2 values(10102, 'ningbo', 101);
insert into test_area2 values(10103, 'shaoxing', 101);
insert into test_area2 values(10104, 'wenzhou', 101);
insert into test_area2 values(1010101, 'binjiang', 10101);
insert into test_area2 values(1010102, 'shangcheng', 10101);
insert into test_area2 values(1010103, 'xihu', 10101);
insert into test_area2 values(1010101, 'gongshu', 10101);
select distinct test_area1.id, test_area1.name, test_area1.pid from test_area1, test_area2 start with name = 'hangzhou' connect by test_area1.id = test_area1.pid;
ERROR: multiple sources is not support in hierarchical queries
LINE 1: ... test_area1.name, test_area1.pid from test_area1, test_area2...
^
HINT: Use [Cross | INNER | LEFT | RIGHT | FULL ] Join instead.
select distinct test_area1.id, test_area1.name, test_area1.pid from test_area1 join test_area2 on test_area1.id = test_area2.id start with test_area1.name = 'hangzhou' connect by test_area1.id = test_area1.pid;
id | name | pid
-------+----------+-----
10101 | hangzhou | 101
(1 row)
select distinct test_area1.id, test_area1.name, test_area1.pid, oracle.regexp_substr(test_area2.name, '[^,]+', 1, 2) as code from test_area1 join test_area2 on test_area1.id = test_area2.id connect by level <= 3 order by test_area1.id;
id | name | pid | code
---------+--------------+-------+------
1 | china | 0 |
101 | zhejiang | 1 |
102 | jiangsu | 1 |
103 | heilongjiang | 1 |
10101 | hangzhou | 101 |
10102 | ningbo | 101 |
10103 | shaoxing | 101 |
10104 | wenzhou | 101 |
1010101 | binjiang | 10101 |
1010101 | gongshu | 10101 |
1010102 | shangcheng | 10101 |
1010103 | xihu | 10101 |
(12 rows)
select distinct test_area1.id, test_area1.name, test_area1.pid, oracle.regexp_substr(test_area2.name, '[^;]+', 1, level) as code from test_area1 join test_area2 on test_area1.id = test_area2.id connect by level <= 3;
id | name | pid | code
---------+--------------+-------+--------------
10101 | hangzhou | 101 |
10102 | ningbo | 101 |
1010102 | shangcheng | 10101 |
101 | zhejiang | 1 | zhejiang
1010103 | xihu | 10101 |
1010101 | binjiang | 10101 |
102 | jiangsu | 1 |
10104 | wenzhou | 101 | wenzhou
1010101 | binjiang | 10101 | binjiang
102 | jiangsu | 1 | jiangsu
1010101 | gongshu | 10101 | gongshu
1010101 | binjiang | 10101 | gongshu
1010101 | gongshu | 10101 |
10103 | shaoxing | 101 |
10103 | shaoxing | 101 | shaoxing
10102 | ningbo | 101 | ningbo
103 | heilongjiang | 1 |
1010102 | shangcheng | 10101 | shangcheng
10101 | hangzhou | 101 | hangzhou
10104 | wenzhou | 101 |
1010103 | xihu | 10101 | xihu
1 | china | 0 | china
101 | zhejiang | 1 |
1 | china | 0 |
103 | heilongjiang | 1 | heilongjiang
1010101 | gongshu | 10101 | binjiang
(26 rows)
create view tr as select distinct test_area1.id, test_area1.name, test_area1.pid, oracle.regexp_substr(test_area2.name, '[^,]+', 1, 2) as code from test_area1 join test_area2 on test_area1.id = test_area2.id connect by level <= 3 order by test_area1.id;
create table t1(id int, name text, pid int, code text);
declare
iCount integer;
begin
insert into t1 select distinct test_area1.id, test_area1.name, test_area1.pid, oracle.regexp_substr(test_area2.name, '[^,]+', 1, 2) as code from test_area1 join test_area2 on test_area1.id = test_area2.id connect by level <= 3 order by test_area1.id;
end;
/
select * from t1;
id | name | pid | code
---------+--------------+-------+------
1 | china | 0 |
101 | zhejiang | 1 |
102 | jiangsu | 1 |
103 | heilongjiang | 1 |
10101 | hangzhou | 101 |
10102 | ningbo | 101 |
10103 | shaoxing | 101 |
10104 | wenzhou | 101 |
1010101 | binjiang | 10101 |
1010101 | gongshu | 10101 |
1010102 | shangcheng | 10101 |
1010103 | xihu | 10101 |
(12 rows)
