logo - 跳到首页

VastbaseG100

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

Menu

SELECT

SELECT用于从表或视图中查询数据。

SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。

本文档只介绍SELECT语句兼容Oracle的特性，原Vastbase的SELECT语法未做删除和修改，详见SELECT。

Vastbase在Oracle兼容模式下，支持SELECT语句如下特性用法：

SELECT … FOR UPDATE

功能描述

SELECT … FOR UPDATE语句用于锁定满足WHERE查询条件的行，以防止其他用户在结束事务之前锁定或更新这些行。

注意事项

  • Vastbase数据库中可配置参数“update lockwait timeout”，该参数表示对单个锁的超时时间，默认为 120 秒。当select for update wait integer语句因为加锁需要进行等待时，其中integer值为等待所有行级锁的总计时长，因此，可能在总时长还未达指定值前，该语句因为等待单个行级锁超时而退出并解锁。

  • Vastbase支持FOR UPDATE后跟表或者视图名，不支持跟列名。

  • 暂不支持SKIP LOCKED选项。

语法格式

SELECT ... FOR UPDATE [SKIP LOCKED|WAIT integer| NOWAIT]

参数说明

  • FOR UPDATE

    不跟任何参数：如果当前查询的结果中存在锁，则该查询一直等待（等待期间用户无法执行其他操作），直到查询结果中的锁被释放后，立即返回查询结果。

  • WAIT integer：

    如果当前查询的结果中存在锁，且超过integer秒后仍未释放锁，则报错，其中integer为等待超时时间。

    • integer

      取值范围：正整数。

    • integer

      取值0：等同NOWAIT。

  • NOWAIT

    当前查询的结果中存在锁，立即报错。

  • SKIP LOCKED

    使用该选项，则忽略有行锁的记录。

示例

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

create table test8(
id number,
orderid number,
productid number,
price number(10,2),
quantity number
);
insert into test8(id,orderid,productid,price,quantity) 
values(1,1,1,6,10),
(2,1,2,4,5),
(3,1,3,10,2),
(4,2,1,3,6),
(5,2,2,4,6);

2、在会话1中，在事务中使用for update加锁，暂不提交。

begin;
select * from test8 where quantity <6 for update;

返回结果为如下：

BEGIN

id | orderid | productid | price | quantity
--------------------------------------------
2 |        1  |          2   | 4 | 5
3 |        1  |          3   | 10 | 2
(2rows)

3、切换至会话2，使用for update nowait 获取同样数据时，提示获取锁失败。

select * from test8 where quantity <6 for update nowait;

返回结果如下：

ERROR:could not obtain lock on row in relation “test8”

4、切换至会话2，使用for update wait 2 获取同样数据，超时（2s）提示获取锁失败。

select * from test8 where quantity <6 for update wait 2;

返回结果如下：

ERROR:  could not obtain lock on row in relation,waitSec = 2000

5、切换至会话2，使用for update wait 600 获取同样数据，n取足够大，阻塞等待会话1事务提交释放锁。

select * from test8 where quantity <6 for update wait 600;

6、切换至会话1，在等待返回结果的600秒内，提交事务，释放锁。

commit;

7、切换至会话2，成功返回查询结果。

id | orderid | productid | price | quantity
--------------------------------------------
2 |        1  |          2   | 4 | 5
3 |        1  |          3   | 10 | 2
(2rows)

使用关键字做列别名

功能描述

Vastbase在select语句中新增关键字month、content、percent和password作为目标字段的别名。

语法格式

SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [output_name ]} [, ...] }
[ FROM from_item [, ...] ]
[ WHERE condition ]
...

参数说明

output_name

字段别名，支持month、content、percent和password关键字。

示例

使用关键字作为列别名。

select 1  month,2  content,3  percent,4  password from dual;

结果返回如下：

month | content | percent | password 
-------+---------+---------+---------
   1 |    2 |    3|    4
(1  rows)

CONNECT BY支持识别外层ROWID

功能描述

Vastbase G100新增兼容Oracle的用法：CONNECT BY支持ROWID在外层查询被识别。具体用法参考示例。

Vastbase数据库表中的每一行数据都有一个唯一的标识符，也可以称为ROWID，ROWID是一个伪列，更多内容参考ROWID数据类型。

示例

示例1： ROWID出现在SELECT的目标列中。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

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

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

3、执行查询语句。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || t.ename "employee", t.empno, t.mgr,
CONNECT_BY_ROOT t.empno "mgr empno",
CONNECT_BY_ROOT t.ename "mgr ename",rowid
FROM EMP_1140719 t
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

返回结果如下：

 LEVEL | employee  | empno | mgr  | mgr empno | mgr ename |        rowid
-------+-----------+-------+------+-----------+-----------+----------------------
     1 | BLAKE     |  7698 | 7839 | 7698      | BLAKE     | ClYAAA==AAAAAA==BgA=
     2 |   ALLEN   |  7499 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==AgA=
     2 |   JAMES   |  7900 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==DAA=
     2 |   MARTIN  |  7654 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==BQA=
     2 |   TURNER  |  7844 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==CgA=
     2 |   WARD    |  7521 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==AwA=
     1 | CLARK     |  7782 | 7839 | 7782      | CLARK     | ClYAAA==AAAAAA==BwA=
     2 |   MILLER  |  7934 | 7782 | 7782      | CLARK     | ClYAAA==AAAAAA==DgA=
     1 | JONES     |  7566 | 7839 | 7566      | JONES     | ClYAAA==AAAAAA==BAA=
     2 |   FORD    |  7902 | 7566 | 7566      | JONES     | ClYAAA==AAAAAA==DQA=
     3 |     SMITH |  7369 | 7902 | 7566      | JONES     | ClYAAA==AAAAAA==AQA=
     2 |   SCOTT   |  7788 | 7566 | 7566      | JONES     | ClYAAA==AAAAAA==CAA=
     3 |     ADAMS |  7876 | 7788 | 7566      | JONES     | ClYAAA==AAAAAA==CwA=
(13 rows)

示例2： 子查询的外层查询中存在ROWID。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

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

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

3、执行查询语句。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || t.ename "employee", t.empno, t.mgr,
CONNECT_BY_ROOT t.empno "mgr empno",
CONNECT_BY_ROOT t.ename "mgr ename",rowid
FROM EMP_1140768 t
WHERE EMPNO NOT IN(
SELECT EMPNO FROM
EMP_1140768 
WHERE DEPTNO <=20
)  
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

返回结果如下：

 LEVEL | employee | empno | mgr  | mgr empno | mgr ename |        rowid
-------+----------+-------+------+-----------+-----------+----------------------
     1 | BLAKE    |  7698 | 7839 | 7698      | BLAKE     | 8VUAAA==AAAAAA==BgA=
     2 |   ALLEN  |  7499 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==AgA=
     2 |   JAMES  |  7900 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==DAA=
     2 |   MARTIN |  7654 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==BQA=
     2 |   TURNER |  7844 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==CgA=
     2 |   WARD   |  7521 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==AwA=
(6 rows)

JOIN连接允许表别名重复

功能描述

Vastbase G100在Oracle兼容模式下，支持使用JOIN连接从多个表中读取数据时，在JOIN条件两端使用重复的表别名。

例如，在下面这个查询语句中，表别名“o”被重复使用：

select o.c1 from table1 o inner join table2 o on o.a = o.b;

完整示例请参考示例1。

JOIN表达式的判定规则

如果表别名的字段在两个表中都存在，数据库会根据从后向前的规则确定别名对应的表，例如：

select o.b2 from alias_t1 o inner join alias_t2 o on o.id = o.a;

对于o.id的表o，从后向前，首先考虑alias_t2表，alias_t2表存在id字段，因此o.id被识别为alias_t2.id; o.a在判断时的逻辑同理，首先考虑alias_t2表，而alias_t2表没有a字段，则下一步考虑alias_t1表，alias_t1表包含a字段，因此o.a被识别为alias_t1.a。

假设join条件的左右两端别名一致，字段一致，例如:

select o.b2 from alias_tl o inner join alias_t2 o on o.id = o.id;

那么根据从后向前的规则，被解释为alias_t2.id = alias_t2.id，因此alias_t1表并没有参与到JOIN的判断中，因此实际使用时不推荐这样写，虽然执行语句时数据库不会报错，但可能会出现期望之外的结果。

同理:

select o.b2 from alias_t1 o inner join alias_t2 o on o.id = o.b;

上述语句中o.id会被判断成alias_t2.id，o.b会被判断成alias_t2.b，表alias_t1也没有参与到JOIN判断中。

注意事项

  • JOIN左右使用相同表别名的原表可以来自不同的schema。

  • 从后向前的规则只适用于INNER JOIN、LEFT JOIN、RIGHT JOIN。在FULL JOIN中，不能出现表达式的字段为两表共有的情况（即表达式左右的字段必须是A表有而B表没有，或是B表有而A表没有的），考虑如下SQL语句:

    select o.b2 from alias_t1 o full join alias_t2 o on o.id= o.id;

    在上述语句中，由于alias_t1和alias_t2都含有id字段，因此这条SQL执行时将会报错。

    对于SELECT后的字段，此规则同样适用：不能出现表达式的字段在两表中都存在的情况。考虑如下几条SQL语句：

    select o.id from alias_t1 o inner join alias_t2 o on o.a= o.b;
select o.* from alias_t1 o inner join alias_t2 o on o.a= o.b;

    select后的字段为o.id，而alias_t1和alias_t2都含有id字段，无法得知o.id的o到底指代的是alias_t1表还是alias_t2表，因此将会报错处理。

    对于select后的字段o.*，更加无法确定o指代的是哪张表，也会报错处理。

    综上，一旦使用了重复的表别名，假设重复的别名为o，那么SQL中出现o.*，schema.o.*，catalog.schema.o.*均会报错。

示例

示例1： 查询时用INNER JOIN连接两张表并使用相同的表别名。

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

create table alias_t1(id int,a int,a2 int);
create table alias_t2(id int,b int,b2 int);
insert into alias_t1 values(1,1,10);
insert into alias_t2 values(1,1,20);
insert into alias_t2 values(5,5,21);

2、使用INNER JOIN进行连接查询，表别名“o”被INNER JOIN左右的两张表重复使用。

select o.a2 from alias_t1 o inner join alias_t2 o on o.a = o.b ;

返回结果如下：

 a2
----
 10
(1 row)

示例2： 在WHERE子句中使用JOIN，并给JOIN左右的表使用相同的表别名。

1、分别创建两张测试表并插入数据。

CREATE TABLE tb_emp (
id int NOT NULL ,
name varchar(200) ,
age int ,
job varchar(200) ,
salary int ,
entrydate date ,
managerid int ,
dept_id int ,
PRIMARY KEY (id)
) ;
INSERT INTO tb_emp VALUES (1, 'jack', 20, '项目经理', 12500, '2005-12-01', 1, 1);
INSERT INTO tb_emp VALUES (2, 'tom', 60, '会计', 48000, '2006-06-01', 7, 3);
INSERT INTO tb_emp VALUES (3, 'lily', 23, '市场总监', 14000, '2009-06-01', 1, 2);
INSERT INTO tb_emp VALUES (4, 'nancy', 56, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO tb_emp VALUES (5, 'leo', 88, '销售总监', 14000, '2004-06-01', 1, 4);
INSERT INTO tb_emp VALUES (6, '张三', 38, '销售', 4600, '2009-06-01', 14, 4);
INSERT INTO tb_emp VALUES (7, '李明', 42, NULL, 2000, '2022-04-25', 1, NULL);

CREATE TABLE tb_dept (
id int NOT NULL ,
name varchar(200) ,
PRIMARY KEY (id)
) ;
INSERT INTO tb_dept VALUES (1, '研发部');
INSERT INTO tb_dept VALUES (2, '市场部');
INSERT INTO tb_dept VALUES (3, '财务部');
INSERT INTO tb_dept VALUES (4, '销售部');

2、对两张表进行连接查询，并返回财务部的最高薪水。

select max(salary) from tb_emp e join tb_dept e on e.dept_id=e.id where e.dept_id=3;

返回结果如下：

  max
-------
 48000
(1 row)

支持ORDER BY后接表达式

功能描述

Vastbase G100在Oracle或者MySQL兼容模式下，SELECT DISTINCT | UNIQUE的查询列表中可以使用ORDER BY子句中未出现的列；ORDER BY子句中支持使用表达式。

注意事项

语法格式

SELECT DISTINCT | UNIQUE
column_name [, ...]
FROM table_name
[WHERE condition]
ORDER BY expression [ASC|DESC];

参数说明

  • DISTINCT | UNIQUE

    从SELECT的结果集中删除所有重复的行，使结果集中的每行都是唯一的。

  • column_name

    指定子查询结果集中显示的列名。

  • table_name

    表名或视图名，名称前可加上模式名，如：schema_name.table_name。

  • [ WHERE condition ]

    WHERE子句构成一个行选择表达式，用来缩小SELECT查询的范围。

  • expression

    ORDER BY表达式，对SELECT语句检索得到的数据进行排序。

  • [ ASC | DESC ]

    查询结果的排序方式是升序（ASC）还是降序（DESC），默认是升序排列。

示例

示例1： 在ORDER BY子句中使用函数。

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

create table tmp(id int,id1 int);
insert into tmp values(5,15);
insert into tmp values(1,20);
insert into tmp values(null,10);
insert into tmp values(null,8);
insert into tmp values(20,7);
insert into tmp values(null,6);
insert into tmp values(null,4);
insert into tmp values(5,5);
insert into tmp values(null,null);
insert into tmp values(null,null);
insert into tmp values(2,2);
insert into tmp values(2,2);

2、查询测试表内容，并在ORDER BY子句中使用NVL函数。

select distinct id,id1 from tmp order by nvl(id,id1);

返回结果如下：

 id | id1
----+-----
  1 |  20
  2 |   2
    |   4
  5 |  15
  5 |   5
    |   6
    |   8
    |  10
 20 |   7
    |
(10 rows)

示例2： 在ORDER BY子句中使用算术表达式。

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

create table test_1009(id varchar(32),name varchar(32),source varchar(20));
insert into test_1009 values('1','zhangsan','100');
insert into test_1009 values('1','wanger','100');
insert into test_1009 values('12','lisi','98');
insert into test_1009 values('4','wangwu','78');
insert into test_1009 values('4','lisi','78');

2、查询测试表内容，在ORDER BY子句中使用算术表达式。

select distinct id from test_1009  order by (id+0),(source+0);

返回结果如下：

 id
----
 1
 4
 12
(3 rows)

开窗语句RANGE BETWEEN AND语法内使用表达式

功能描述

Vastbase在Oracle兼容模式下支持查询语句中的开窗语句RANGE BETWEEN AND语法内使用表达式，其中表达式的结果可以是0、正数和时间区间。

注意事项

  • 本语法不支持与KEEP一起使用。

  • 不支持order by后接number类型字段。

  • RANGE BETWEEN AND中必须有且仅包含有一个ORDER BY。

  • 暂不支持order by列类型与range between offset preceding的offset类型不相同（例如order by b range between 1.1 following and 1.09 following（其中b是int）)。

  • 暂不支持range between小数类型和number类型。

  • frame_start缺省为UNBOUNDED PRECEDING，frame_end缺省为CURRENT ROW。

  • frame_end的取值在上面列出的顺序中必须晚于frame_start的取值

  • value不可为负数或空，可以为0

  • 如下语法是不合理的，所以被禁止：

    between unbounded following and xxx
between xxx and unbounded preceding
between xx following and xx preceding
between current row and xx preceding
between xx following and current row

语法格式

RANGE BETWEEN frame_start and frame_end

其中frame_start和 frame_end可以是以下五种：

UNBOUNDED PRECEDING 
UNBOUNDED FOLLOWING 
CURRENT ROW  
expr PRECEDING    
expr FOLLOWING

参数说明

  • UNBOUNDED PRECEDING

    指定窗口在分区中的第一行开始，即窗口的边界是分区中的第一行。

  • UNBOUNDED FOLLOWING

    指定窗口在分区的最后一行结束，即窗口的边界是分区中的最后一行。

  • CURRENT ROW

    指定窗口在当前行开始或结束，即窗口的边界是当前行或者当前行的值。

  • expr PRECEDING

    指定要置于当前行之前的行或值的数目，即窗口的边界是当前行向前滑动expr的行或当前值-expr的值。

  • expr FOLLOWING

    指定窗口在分区的最后一行结束，即窗口的边界是当前行向后滑动 expr的行或当前值+expr的值

示例

示例1 聚合函数和开窗函数结合，且开窗语句为RANGE BETWEEN UNBOUNDED PRECEDING AND expr preceding表达式。

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

CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount int
);

INSERT INTO sales (sale_id, sale_date, amount) VALUES (1, date '2023-07-01', 100);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (2, date '2023-07-02', 150);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (3, date '2023-07-03', 200);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (4, date '2023-07-04', 250);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (5, date '2023-07-05', 300);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (6, date '2023-07-06', 350);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (7, date '2023-07-07', 400);

2、查看当前表数据。

select * from sales;

返回结果为如下：

 sale_id |      sale_date      | amount
---------+---------------------+--------
       1 | 2023-07-01 00:00:00 |    100
       2 | 2023-07-02 00:00:00 |    150
       3 | 2023-07-03 00:00:00 |    200
       4 | 2023-07-04 00:00:00 |    250
       5 | 2023-07-05 00:00:00 |    300
       6 | 2023-07-06 00:00:00 |    350 
       7 | 2023-07-07 00:00:00 |    400       
(7 rows)

3、执行如下查询，其中sum_amount列的结果为将sale_date列进行排序，当前行进行-3计算后所得行及之前所有行的amount列值累加。

SELECT
sale_id,
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) AS sum_amount
FROM
sales;

查询结果返回为如下，如sale_id为5时，sum_amount为5-3计算得到的第二行及之前的所有行将amount列值累加：

 sale_id |      sale_date      | amount | sum_amount
---------+---------------------+--------+------------
       1 | 2023-07-01 00:00:00 |    100 |
       2 | 2023-07-02 00:00:00 |    150 |
       3 | 2023-07-03 00:00:00 |    200 |
       4 | 2023-07-04 00:00:00 |    250 |        100
       5 | 2023-07-05 00:00:00 |    300 |        250
       6 | 2023-07-06 00:00:00 |    350 |        450
       7 | 2023-07-07 00:00:00 |    400 |        700      
(7 rows)

示例2： 开窗语句为RANGE BETWEEN expr PRECEDING AND expr FOLLOWING，其中的expr为算数表达式。

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

CREATE TABLE emp (
empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal int CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBER(7,2),
deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

2、查询测试表中数据。

select * from emp;

返回结果为如下：

 empno | ename  |    job    | mgr  |      hiredate       | sal  | comm | deptno
-------+--------+-----------+------+---------------------+------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 |      |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 |      |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 |      |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 |      |     10
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 |      |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000 |      |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30
  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 |      |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 |      |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 |      |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |      |     10
(14 rows)

3、执行如下查询语句。

SELECT ENAME, SAL, SAL*.9 LOW, SAL*1.1 HIGH,COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN SAL*2 PRECEDING AND SAL*2 FOLLOWING) COUNT FROM EMP;

返回结果为如下：

 ename  | sal  |  low   |  high  | count
--------+------+--------+--------+-------
 SMITH  |  800 |    720 |    880 |     8
 JAMES  |  950 |    855 |   1045 |    10
 ADAMS  | 1100 |    990 |   1210 |    13
 WARD   | 1250 |   1125 |   1375 |    13
 MARTIN | 1250 |   1125 |   1375 |    13
 MILLER | 1300 |   1170 |   1430 |    13
 TURNER | 1500 |   1350 |   1650 |    13
 ALLEN  | 1600 |   1440 |   1760 |    13
 CLARK  | 2450 |   2205 |   2695 |    14
 BLAKE  | 2850 |   2565 |   3135 |    14
 JONES  | 2975 | 2677.5 | 3272.5 |    14
 FORD   | 3000 |   2700 |   3300 |    14
 SCOTT  | 3000 |   2700 |   3300 |    14
 KING   | 5000 |   4500 |   5500 |    14
(14 rows)

外连接（+）

功能描述

Vastbase在Oracle兼容模式下支持外连接功能，且从Build 14开始支持使用(+)实现如下特性：

  • 一表对多的连接特性，即单表不同字段外连接不同表的不同字段。

    • 在多个条件连接中使用多表的不同字段。可参见示例1。

    • 在复杂表达式中使用多表不同字段。可参见示例2。

  • 多对多表的连接特性，即多表相互连接。如t1 OUTER JOIN t2与t3时，t2依然可以 OUTER JOIN t3。可参见示例3。

本特性在SQL语句中的表现为：

where子句的相等条件中，某一端的单表字段带有(+)，且另一端的表并非同一张表。

注意事项

  • 关于使用(+)的一些注意事项：

    • (+)操作符只能出现在where子句中，并且不能与outer join语法同时使用。
    • 当使用(+)操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含(+)操作符。
    • (+)操作符只适用于列，而不能用在表达式上。
    • (+)操作符不能与or和in操作符一起使用。
    • (+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

  • 不支持SQL语句中存在有向环的外连接。例如以下SQL语句中存在t1->t2->t3->t1的有向环，会执行失败：

    SELECT * FROM t1,t2,t3 WHERE t1.a(+)=t2.a and t2.b(+)=t3.b and t3.c(+)=t1.c;

示例

示例1 在多个条件连接中使用不同字段。

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

create table t1(id int,str varchar(50));
create table t2(id int,str varchar(50));
create table t3(id int,str varchar(50));
insert into t1 values(1,'aaa');
insert into t1 values(11,'a1');
insert into t2 values(1,'bbb');
insert into t2 values(12,'b1');
insert into t3 values(3,'aaa');
insert into t3 values(13,'c1');
insert into t3 values(100,'b1');

2、使用外连接查询。

select * from t1,t2,t3 where t1.id(+)=t2.id and t3.str=t1.str(+);

查询结果为如下：

 id | str | id | str | id  | str
----+-----+----+-----+-----+-----
  1 | aaa |  1 | bbb |   3 | aaa
    |     |  1 | bbb |  13 | c1
    |     |  1 | bbb | 100 | b1
    |     | 12 | b1  |   3 | aaa
    |     | 12 | b1  |  13 | c1
    |     | 12 | b1  | 100 | b1
(6 rows)

示例2 在复杂表达式中使用多表不同字段。

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

create table t23(id int,id2 int,str varchar(50));
create table t24(id int,id2 int,str varchar(50));
create table t25(id int,id2 int,str varchar(50));
insert into t23 values(10,100,'aaa');
insert into t23 values(230,230,'t230');
insert into t24 values(3,70,'t240');
insert into t24 values(3,0,'t241');
insert into t25 values(7,251,'aaa');
insert into t25 values(250,250,'t250');

2、使用外连接查询。

select * from t23,t24,t25 where t23.id(+)=t24.id + t25.id;

查询结果为如下：

 id | id2 | str | id | id2 | str  | id  | id2 | str
----+-----+-----+----+-----+------+-----+-----+------
 10 | 100 | aaa |  3 |  70 | t240 |   7 | 251 | aaa
    |     |     |  3 |  70 | t240 | 250 | 250 | t250
 10 | 100 | aaa |  3 |   0 | t241 |   7 | 251 | aaa
    |     |     |  3 |   0 | t241 | 250 | 250 | t250
(4 rows)

示例3 多表相互连接

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

CREATE TABLE t1(id int,str varchar(50),a int,b int);
CREATE TABLE t2(id int,str varchar(50),a int,b int);
CREATE TABLE t3(id int,str varchar(50),a int,b int);
INSERT INTO t1 VALUES (1,'aaa',42,70);
INSERT INTO t1 VALUES(11,'a1',43,71);
INSERT INTO t2 VALUES(1,'bbb',60,70);
INSERT INTO t2 VALUES(12,'b1',44,72);
INSERT INTO t3 VALUES(3,'aaa',60,73);
INSERT INTO t3 VALUES(13,'c1',45,74);
INSERT INTO t3 VALUES(100,'b1',46,75);

2、执行多表外连接查询。

SELECT * FROM t1,t2,t3 WHERE t1.id(+)=t2.id and t1.str(+)=t3.str and t2.a(+)=t3.a;

结果返回如下：

 id | str | a  | b  | id | str | a  | b  | id  | str | a  | b
----+-----+----+----+----+-----+----+----+-----+-----+----+----
  1 | aaa | 42 | 70 |  1 | bbb | 60 | 70 |   3 | aaa | 60 | 73
    |     |    |    |    |     |    |    |  13 | c1  | 45 | 74
    |     |    |    |    |     |    |    | 100 | b1  | 46 | 75
(3 rows)