在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 用于返回唯一不同的值。它可以用于单个列或多个列，以消除结果集中的重复值。例如，以下查询将返回一个列表中不同的城市名称：
SELECT DISTINCT city_name FROM table_name;CONNECT BY 用于在查询中生成层次结构。它基于表中的父子关系列（通常是自引用的外键列），递归地连接行，以构建树状结构。以下是一个示例查询，假设有一个名为 "employees" 的表，其中包含员工的 ID、姓名和上级的 ID：
SELECT employee_id, employee_name, manager_id, LEVEL FROM employees START WITH employee_id = 1 CONNECT BY PRIOR employee_id = manager_id;上述查询将从具有 ID 为 1 的员工开始，逐级向上连接上级，直到达到树的根节点。LEVEL 列表示每个员工在层次结构中的级别。
在LightDB中两者具体使用举例如下：
lightdb@t1=# create table test_area(id int, name text, pid int);
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 fromCREATE TABLE
lightdb@t1=# insert into test_area values(1, 'china', 0);
INSERT 0 1
lightdb@t1=# insert into test_area values(101, 'zhejiang', 1);
INSERT 0 1
lightdb@t1=# insert into test_area values(102, 'jiangsu', 1);
INSERT 0 1
lightdb@t1=# insert into test_area values(103, 'heilongjiang', 1);
INSERT 0 1
lightdb@t1=# insert into test_area values(10101, 'hangzhou', 101);
INSERT 0 1
lightdb@t1=# insert into test_area values(10102, 'ningbo', 101);
INSERT 0 1
lightdb@t1=# insert into test_area values(10103, 'shaoxing', 101);
INSERT 0 1
lightdb@t1=# insert into test_area values(10104, 'wenzhou', 101);
INSERT 0 1
lightdb@t1=# insert into test_area values(1010101, 'binjiang', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area values(1010102, 'shangcheng', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area values(1010103, 'xihu', 10101);
v_testarea start with name = 'hangzhou' connect by pid = prior id;
drop view v_testarea;
drop table test_area;
create table test_area1(id int, name text, pid int);
insert into test_area1 values(1, 'china', 0);
insert into test_area1 values(101, 'zhejiangINSERT 0 1
lightdb@t1=# insert into test_area values(1010101, 'gongshu', 10101);
INSERT 0 1
lightdb@t1=# create view v_testarea as select * from test_area;
CREATE VIEW
lightdb@t1=# 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)
lightdb@t1=# drop view v_testarea;
DROP VIEW
lightdb@t1=# drop table test_area;
', 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,DROP TABLE
lightdb@t1=#
lightdb@t1=# create table test_area1(id int, name text, pid int);
CREATE TABLE
lightdb@t1=# insert into test_area1 values(1, 'china', 0);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(101, 'zhejiang', 1);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(102, 'jiangsu', 1);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(103, 'heilongjiang', 1);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(10101, 'hangzhou', 101);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(10102, 'ningbo', 101);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(10103, 'shaoxing', 101);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(10104, 'wenzhou', 101);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(1010101, 'binjiang', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(1010102, 'shangcheng', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(1010103, 'xihu', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area1 values(1010101, 'gongshu', 10101);
INSERT 0 1
lightdb@t1=# 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)
lightdb@t1=# create table test_area2(id int, name text, pid int);
CREATE TABLE
lightdb@t1=# insert into test_area2 values(1, 'china', 0);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(101, 'zhejiang', 1);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(102, 'jiangsu', 1);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(103, 'heilongjiang', 1);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(10101, 'hangzhou', 101);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(10102, 'ningbo', 101);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(10103, 'shaoxing', 101);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(10104, 'wenzhou', 101);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(1010101, 'binjiang', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(1010102, 'shangcheng', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(1010103, 'xihu', 10101);
INSERT 0 1
lightdb@t1=# insert into test_area2 values(1010101, 'gongshu', 10101);
INSERT 0 1
lightdb@t1=# 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.
lightdb@t1=# 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)
lightdb@t1=#
lightdb@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;
cle.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;
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 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 | gongshu | 10101 |
1010101 | binjiang | 10101 |
1010102 | shangcheng | 10101 |
1010103 | xihu | 10101 |
(12 rows)
lightdb@t1=# 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;
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
---------+--------------+-------+--------------
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)
lightdb@t1=# 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 VIEW
lightdb@t1=#
lightdb@t1=# create table t1(id int, name text, pid int, code text);
CREATE TABLE
lightdb@t1=# declare
lightdb@t1$# iCount integer;
lightdb@t1$# begin
lightdb@t1$# 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;
lightdb@t1$# end;
lightdb@t1$# /
DO
lightdb@t1=# 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 | gongshu | 10101 |
1010101 | binjiang | 10101 |
1010102 | shangcheng | 10101 |
1010103 | xihu | 10101 |
(12 rows)