3.2.3.6. OceanBase-Oracle
3.2.3.6.1. 分区表查询
CREATE TABLE unisql_partition (
unisql_id NUMBER,
unisql_name VARCHAR2(50),
unisql_salary NUMBER(10,2),
unisql_hire_date DATE,
CONSTRAINT unisql_partition_pk PRIMARY KEY (unisql_id, unisql_hire_date)
)
PARTITION BY RANGE (unisql_hire_date) (
PARTITION unisql_partition_p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (1, 'John', 5000, TO_DATE('2023-01-01','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (2, 'Mary', 6000, TO_DATE( '2023-02-15','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (3, 'Tom', 7000, TO_DATE( '2023-03-20','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (4, 'Alice', 8000, TO_DATE('2023-04-10','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (5, 'Bob', 9000, TO_DATE('2023-05-01','YYYY-MM-DD'));
-- 转换前Oracle SQL：
SELECT unisql_id,unisql_name,unisql_salary, unisql_hire_date FROM unisql_partition PARTITION (unisql_partition_p1);
UNISQL_ID|UNISQL_NAME|UNISQL_SALARY|UNISQL_HIRE_DATE |
---------+-----------+-------------+-----------------------+
1|John | 5000|2023-01-01 00:00:00.000|
2|Mary | 6000|2023-02-15 00:00:00.000|
3|Tom | 7000|2023-03-20 00:00:00.000|
4|Alice | 8000|2023-04-10 00:00:00.000|
5|Bob | 9000|2023-05-01 00:00:00.000|
-- 转换后GaussDB-Oracle SQL：
SELECT unisql_id,unisql_name,unisql_salary,unisql_hire_date FROM unisql_partition_1_prt_unisql_partition_p1
unisql_id|unisql_name|unisql_salary|unisql_hire_date |
---------+-----------+-------------+-----------------------+
1|John | 5000.00|2023-01-01 00:00:00.000|
2|Mary | 6000.00|2023-02-15 00:00:00.000|
3|Tom | 7000.00|2023-03-20 00:00:00.000|
4|Alice | 8000.00|2023-04-10 00:00:00.000|
5|Bob | 9000.00|2023-05-01 00:00:00.000|
create table unisql_partition_by_list_test
(
id varchar2(15) not null,
city varchar2(20),
city_number NUMBER(10,2)
)
partition by list (city)(
partition "P1" values ('beijing'),
partition p2 values ('shanghai'),
partition p3 values ('changsha'),
partition p4 values (default)
);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (1, 'beijing', 5000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (2, 'beijing', 9000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (3, 'beijing', 2000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (4, 'shanghai', 3000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (5, 'hangzhou', 6000);
-- 转换前Oracle SQL：
SELECT id, city, city_number FROM unisql_partition_by_list_test PARTITION ("P1");
ID|CITY |CITY_NUMBER|
--+-------+-----------+
1 |beijing| 5000|
2 |beijing| 9000|
3 |beijing| 2000|
-- 转换后GaussDB-Oracle SQL：
SELECT id, city,city_number FROM "unisql_partition_by_list_test_1_prt_P1"
id|city |city_number|
--+-------+-----------+
1 |beijing| 5000.00|
2 |beijing| 9000.00|
3 |beijing| 2000.00|
3.2.3.6.2. pivot行转列
CREATE TABLE unisql_pivot(id int ,name varchar(64),score NUMBER ,subject varchar2(64));
INSERT INTO unisql_pivot VALUES(1,'张三',70,'CHINESE');
INSERT INTO unisql_pivot VALUES(1,'张三',90,'MATH');
INSERT INTO unisql_pivot VALUES(1,'张三',95,'ENGLISH');
INSERT INTO unisql_pivot VALUES(2,'李四',75,'CHINESE');
INSERT INTO unisql_pivot VALUES(2,'李四',85,'MATH');
INSERT INTO unisql_pivot VALUES(2,'李四',90,'ENGLISH');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'CHINESE');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'MATH');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'ENGLISH');
-- 转换前Oracle SQL：
SELECT id,name,chinese_score,match_score,english_score
FROM unisql_pivot
pivot(
min(score) FOR subject IN('CHINESE' AS chinese_score,'MATH' AS match_score,'ENGLISH' AS english_score)
)
WHERE id IN (1, 2, 3) ORDER BY id;
ID|NAME|CHINESE_SCORE|MATCH_SCORE|ENGLISH_SCORE|
--+----+-------------+-----------+-------------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
-- 转换后GaussDB-Oracle SQL：
SELECT id,name,min(score) FILTER (WHERE subject='CHINESE') AS chinese_score,min(score) FILTER (WHERE subject='MATH') AS match_score,min(score) FILTER (WHERE subject='ENGLISH') AS english_score FROM unisql_pivot WHERE id IN (1,2,3) GROUP BY id,name ORDER BY id
id|name|chinese_score|match_score|english_score|
--+----+-------------+-----------+-------------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
3.2.3.6.3. unpivot列转行
-- 建表语句
CREATE TABLE unisql_unpivot(id INT,name VARCHAR(64),chinese NUMBER,math INT,english INT);
INSERT INTO unisql_unpivot VALUES(1,'张三',70,90,95);
INSERT INTO unisql_unpivot VALUES(2,'李四',75,85,90);
INSERT INTO unisql_unpivot VALUES(3,'张三',90,90,90);
-- 转换前Oracle SQL:
SELECT
id,
name,
score ,
subject
FROM
unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english))
WHERE
id IN (1, 2, 3) ORDER BY id;
ID|NAME|SCORE|SUBJECT|
--+----+-----+-------+
1|张三 | 70|CHINESE|
1|张三 | 90|MATH |
1|张三 | 95|ENGLISH|
2|李四 | 75|CHINESE|
2|李四 | 85|MATH |
2|李四 | 90|ENGLISH|
3|张三 | 90|CHINESE|
3|张三 | 90|MATH |
3|张三 | 90|ENGLISH|
-- 转换后GaussDB-Oracle SQL:
SELECT id,name,score,subject FROM unisql_unpivot, LATERAL(VALUES(chinese, 'CHINESE'), (math, 'MATH'), (english, 'ENGLISH')) AS un_pivot(score, subject) WHERE id IN (1,2,3) ORDER BY id
id|name|score|subject|
--+----+-----+-------+
1|张三 | 70|CHINESE|
1|张三 | 90|MATH |
1|张三 | 95|ENGLISH|
2|李四 | 75|CHINESE|
2|李四 | 85|MATH |
2|李四 | 90|ENGLISH|
3|张三 | 90|CHINESE|
3|张三 | 90|MATH |
3|张三 | 90|ENGLISH|
3.2.3.6.3.1. 层次查询
层次查询（Hierarchical Query）是一种具有特殊功能的查询语句，通过它能够将分层数据按照层次关系展示出来。
语法
SELECT [level], column, expr... FROM table [WHERE condition] [ START WITH start_expression ]
CONNECT BY { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ]
警告
层次查询特有表达式不支持嵌套使用：如: sys_connect_by_root(prior column)、LTRIM(MAX(SYS_CONNECT_BY_PATH(column, ‘;’)), ‘;’)
不支持和oracle(+)一起使用
不支持和pivot, unpivot函数一起使用
列名不支持带schema名和数据库名，只支持表名和字段名。
不支持和with cte一起使用
多表不支持*，需要列明字段；单表用*时，不能再查表中相同字段，如test中有id字段，不能用test.*,test.id，test.id
不支持函数
level、CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH 中不支持表达式和函数，只支持字段
不支持rownum
3.2.3.6.4. connect by
DROP TABLE unisql_connect_by_emp;
CREATE TABLE unisql_connect_by_emp (empno int NOT NULL,ename VARCHAR2(10),job VARCHAR2(9),mgr int,sal int,comm int,deptno int);
INSERT INTO unisql_connect_by_emp VALUES (7369,'SMITH','CLERK',7902,800,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7499,'ALLEN','SALESMAN',7698,1600,300,30);
INSERT INTO unisql_connect_by_emp VALUES (7521,'WARD','SALESMAN',7698,1250,500,30);
INSERT INTO unisql_connect_by_emp VALUES (7566,'JONES','MANAGER',7839,2975,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7654,'MARTIN','SALESMAN',7698,1250,1400,30);
INSERT INTO unisql_connect_by_emp VALUES (7698,'BLAKE','MANAGER',7839,2850,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7782,'CLARK','MANAGER',7839,2450,NULL,10);
INSERT INTO unisql_connect_by_emp VALUES (7788,'SCOTT','ANALYST',7566,3000,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7839,'KING','PRESIDENT',NULL,5000,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7844,'TURNER','SALESMAN',7698,1500,0,30);
INSERT INTO unisql_connect_by_emp VALUES (7876,'ADAMS','CLERK',7788,1100,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7900,'JAMES','CLERK',7698,950,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7902,'FORD','ANALYST',7566,3000,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7934,'MILLER','CLERK',7782,1300,NULL,10);
-- 转换前Oracle SQL:
-- 单表
SELECT LEVEL, empno, ename, mgr, sal,connect_by_root(DEPTNO) root,
SYS_CONNECT_BY_PATH(LEVEL,'/') FROM unisql_connect_by_emp START WITH ename = 'BLAKE' CONNECT BY PRIOR empno = mgr ;
LEVEL|EMPNO|ENAME |MGR |SAL |ROOT|SYS_CONNECT_BY_PATH(LEVEL,'/')|
-----+-----+------+----+----+----+------------------------------+
1| 7698|BLAKE |7839|2850| 30|/1 |
2| 7499|ALLEN |7698|1600| 30|/1/2 |
2| 7521|WARD |7698|1250| 30|/1/2 |
2| 7654|MARTIN|7698|1250| 30|/1/2 |
2| 7844|TURNER|7698|1500| 30|/1/2 |
2| 7900|JAMES |7698| 950| 30|/1/2 |
-- 转换后GaussDB-Oracle SQL:
WITH RECURSIVE tmp AS (SELECT 1 AS "$level",empno AS "$empno",ename AS "$ename",mgr AS "$mgr",sal AS "$sal",DEPTNO AS root,concat('/', 1) AS sys_connect_by_path_level,NULL AS "prior$empno" FROM unisql_connect_by_emp WHERE ename='BLAKE' UNION ALL SELECT "$level"+1 AS "$level",unisql_connect_by_emp.empno AS "$empno",unisql_connect_by_emp.ename AS "$ename",unisql_connect_by_emp.mgr AS "$mgr",unisql_connect_by_emp.sal AS "$sal",root AS root,concat(concat(sys_connect_by_path_level, '/'), "$level"+1) AS sys_connect_by_path_level,CAST("$empno" AS text) AS "prior$empno" FROM unisql_connect_by_emp , tmp WHERE tmp."$empno"=unisql_connect_by_emp.mgr) SELECT tmp."$level" AS LEVEL,tmp."$empno" AS empno,tmp."$ename" AS ename,tmp."$mgr" AS mgr,tmp."$sal" AS sal,tmp.root AS root,tmp.sys_connect_by_path_level AS sys_connect_by_path_level FROM tmp
level|empno|ename |mgr |sal |root|sys_connect_by_path_level|
-----+-----+------+----+----+----+-------------------------+
1| 7698|BLAKE |7839|2850| 30|/1 |
2| 7499|ALLEN |7698|1600| 30|/1/2 |
2| 7521|WARD |7698|1250| 30|/1/2 |
2| 7654|MARTIN|7698|1250| 30|/1/2 |
2| 7844|TURNER|7698|1500| 30|/1/2 |
2| 7900|JAMES |7698| 950| 30|/1/2 |
-- 转换前Oracle SQL:
-- 表关联
select
emp_.empno,
emp_.ename,
emp_.job as jobnew,
emp_.mgr,
emp_.sal,
emp_.comm,
LEVEL,
connect_by_root(emp_.DEPTNO) AS root,
SYS_CONNECT_BY_PATH(emp_.deptno,'/') path,
SYS_CONNECT_BY_PATH(level,'/')
from unisql_connect_by_emp emp_
left join unisql_connect_by_emp emp2 on emp_.empno=emp2.empno
start with emp_.mgr is null connect by PRIOR emp_.empno = emp_.mgr;
EMPNO|ENAME |JOBNEW |MGR |SAL |COMM|LEVEL|ROOT|PATH |SYS_CONNECT_BY_PATH(LEVEL,'/')|
-----+------+---------+----+----+----+-----+----+------------+------------------------------+
7839|KING |PRESIDENT| |5000| | 1| 30|/30 |/1 |
7566|JONES |MANAGER |7839|2975| | 2| 30|/30/20 |/1/2 |
7788|SCOTT |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7876|ADAMS |CLERK |7788|1100| | 4| 30|/30/20/20/20|/1/2/3/4 |
7902|FORD |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7369|SMITH |CLERK |7902| 800| | 4| 30|/30/20/20/20|/1/2/3/4 |
7698|BLAKE |MANAGER |7839|2850| | 2| 30|/30/30 |/1/2 |
7499|ALLEN |SALESMAN |7698|1600| 300| 3| 30|/30/30/30 |/1/2/3 |
7521|WARD |SALESMAN |7698|1250| 500| 3| 30|/30/30/30 |/1/2/3 |
7654|MARTIN|SALESMAN |7698|1250|1400| 3| 30|/30/30/30 |/1/2/3 |
7844|TURNER|SALESMAN |7698|1500| 0| 3| 30|/30/30/30 |/1/2/3 |
7900|JAMES |CLERK |7698| 950| | 3| 30|/30/30/30 |/1/2/3 |
7782|CLARK |MANAGER |7839|2450| | 2| 30|/30/10 |/1/2 |
7934|MILLER|CLERK |7782|1300| | 3| 30|/30/10/10 |/1/2/3 |
-- 转换后GaussDB-Oracle SQL:
WITH RECURSIVE tmp AS (SELECT emp_.empno AS "emp_$empno",emp_.ename AS "emp_$ename",emp_.job AS jobnew,emp_.mgr AS "emp_$mgr",emp_.sal AS "emp_$sal",emp_.comm AS "emp_$comm",1 AS "$level",emp_.DEPTNO AS root,concat('/', emp_.deptno) AS path,concat('/', 1) AS sys_connect_by_path_level,NULL AS "prioremp_$empno" FROM unisql_connect_by_emp AS emp_ LEFT JOIN unisql_connect_by_emp AS emp2 ON emp_.empno=emp2.empno WHERE emp_.mgr IS NULL UNION ALL SELECT tmp_tmp."emp_$empno" AS "emp_$empno",tmp_tmp."emp_$ename" AS "emp_$ename",tmp_tmp.jobnew AS jobnew,tmp_tmp."emp_$mgr" AS "emp_$mgr",tmp_tmp."emp_$sal" AS "emp_$sal",tmp_tmp."emp_$comm" AS "emp_$comm","$level"+1 AS "$level",root AS root,concat(concat(path, '/'), tmp_tmp."emp_$deptno") AS path,concat(concat(sys_connect_by_path_level, '/'), "$level"+1) AS sys_connect_by_path_level,CAST(tmp_tmp."emp_$empno" AS text) AS "prioremp_$empno" FROM (SELECT emp_.empno AS "emp_$empno",emp_.ename AS "emp_$ename",emp_.job AS jobnew,emp_.mgr AS "emp_$mgr",emp_.sal AS "emp_$sal",emp_.comm AS "emp_$comm",emp_.deptno AS "emp_$deptno",emp_.empno AS "prioremp_$empno" FROM unisql_connect_by_emp AS emp_ LEFT JOIN unisql_connect_by_emp AS emp2 ON emp_.empno=emp2.empno) AS tmp_tmp , tmp WHERE tmp."emp_$empno"=tmp_tmp."emp_$mgr") SELECT tmp."emp_$empno" AS empno,tmp."emp_$ename" AS ename,tmp.jobnew AS jobnew,tmp."emp_$mgr" AS mgr,tmp."emp_$sal" AS sal,tmp."emp_$comm" AS comm,tmp."$level" AS LEVEL,tmp.root AS root,tmp.path AS path,tmp.sys_connect_by_path_level AS sys_connect_by_path_level FROM tmp
empno|ename |jobnew |mgr |sal |comm|level|root|path |sys_connect_by_path_level|
-----+------+---------+----+----+----+-----+----+------------+-------------------------+
7839|KING |PRESIDENT| |5000| | 1| 30|/30 |/1 |
7566|JONES |MANAGER |7839|2975| | 2| 30|/30/20 |/1/2 |
7698|BLAKE |MANAGER |7839|2850| | 2| 30|/30/30 |/1/2 |
7782|CLARK |MANAGER |7839|2450| | 2| 30|/30/10 |/1/2 |
7499|ALLEN |SALESMAN |7698|1600| 300| 3| 30|/30/30/30 |/1/2/3 |
7521|WARD |SALESMAN |7698|1250| 500| 3| 30|/30/30/30 |/1/2/3 |
7654|MARTIN|SALESMAN |7698|1250|1400| 3| 30|/30/30/30 |/1/2/3 |
7788|SCOTT |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7844|TURNER|SALESMAN |7698|1500| 0| 3| 30|/30/30/30 |/1/2/3 |
7900|JAMES |CLERK |7698| 950| | 3| 30|/30/30/30 |/1/2/3 |
7902|FORD |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7934|MILLER|CLERK |7782|1300| | 3| 30|/30/10/10 |/1/2/3 |
7369|SMITH |CLERK |7902| 800| | 4| 30|/30/20/20/20|/1/2/3/4 |
7876|ADAMS |CLERK |7788|1100| | 4| 30|/30/20/20/20|/1/2/3/4 |
-- 转换前Oracle SQL:
-- 构造列数
select level from dual connect by level<=10;
LEVEL|
-----+
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|
-- 转换后GaussDB-Oracle SQL:
WITH RECURSIVE tmp AS (SELECT 1 AS "$level" UNION ALL SELECT "$level"+1 AS "$level" FROM tmp WHERE "$level"+1<=10) SELECT tmp."$level" AS level FROM tmp
level|
-----+
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|
3.2.3.6.4.1. 复合查询
使用集合运算符 UNION、UNION ALL、INTERSECT 和 MINUS 来组合多个查询，即复合查询。所有集合运算符都具有相同的优先级。
3.2.3.6.5. UNION 唯一并集
{ (< SQL- 查询语句 1>) }
UNION
{ (< SQL- 查询语句 2>) }
CREATE TABLE unisql_collect_1(
id INT,
name VARCHAR(32),
chinese NUMBER,
math NUMBER,
english NUMBER);
CREATE TABLE unisql_collect_2(
id INT,
name VARCHAR(32),
chinese NUMBER,
math NUMBER,
english NUMBER);
INSERT INTO unisql_collect_1 VALUES(1,'张三',70,90,95);
INSERT INTO unisql_collect_1 VALUES(2,'李四',75,85,90);
INSERT INTO unisql_collect_1 VALUES(3,'王五',90,90,90);
INSERT INTO unisql_collect_1 VALUES(4,'秦六',65,65,60);
INSERT INTO unisql_collect_2 VALUES(1,'张三',70,90,95);
INSERT INTO unisql_collect_2 VALUES(2,'李四',75,85,90);
INSERT INTO unisql_collect_2 VALUES(4,'Bob',78,88,65);
INSERT INTO unisql_collect_2 VALUES(5,'Li',90,93,93);
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
UNION
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
4|Bob | 78| 88| 65|
4|秦六 | 65| 65| 60|
5|Li | 90| 93| 93|
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM unisql_collect_1 UNION SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
4|Bob | 78| 88| 65|
5|Li | 90| 93| 93|
4|秦六 | 65| 65| 60|
3|王五 | 90| 90| 90|
2|李四 | 75| 85| 90|
1|张三 | 70| 90| 95|
3.2.3.6.6. UNION ALL 并集
{ (< SQL- 查询语句 1>) }
UNION ALL
{ (< SQL- 查询语句 2>) }
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
UNION ALL
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
4|秦六 | 65| 65| 60|
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
4|Bob | 78| 88| 65|
5|Li | 90| 93| 93|
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM unisql_collect_1 UNION ALL SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
4|秦六 | 65| 65| 60|
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
4|Bob | 78| 88| 65|
5|Li | 90| 93| 93|
3.2.3.6.7. INTERSECT 交集
{ (< SQL- 查询语句 1>) }
INTERSECT
{ (< SQL- 查询语句 2>) }
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
INTERSECT
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM unisql_collect_1 INTERSECT SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
2|李四 | 75| 85| 90|
1|张三 | 70| 90| 95|
3.2.3.6.8. MINUS 差集
{ (< SQL- 查询语句 1>) }
MINUS
{ (< SQL- 查询语句 2>) }
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
MINUS
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
3|王五 | 90| 90| 90|
4|秦六 | 65| 65| 60|
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM unisql_collect_1 EXCEPT SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
4|秦六 | 65| 65| 60|
3|王五 | 90| 90| 90|
3.2.3.6.8.1. 联接查询
联接（Join）是将两个或多个表、视图的结合在一起的查询。
3.2.3.6.9. 等值联接
等值联接是包含等值运算符的联接。在进行等值联接时将指定列满足等值条件的行进行组合输出。
示例
-- 创建 unisql_orders 表
CREATE TABLE unisql_orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
);
-- 创建 unisql_customers 表
CREATE TABLE unisql_customers (
customer_id NUMBER,
customer_name VARCHAR2(100),
city VARCHAR2(100)
);
-- 插入示例数据
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000);
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500);
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1001, 'John Smith', 'New York');
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1002, 'Jane Doe', 'Los Angeles');
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
-- 转换后GaussDB-Oracle SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3.2.3.6.10. 自联接
自联接是表与其自身的联接。该表在 FROM 子句中出现两次，后跟表别名，这些别名限定联接条件中的列名。
示例
-- 创建 employees 表
CREATE TABLE unisql_employees (
employee_id NUMBER,
employee_name VARCHAR2(100),
manager_id NUMBER
);
-- 插入示例数据
INSERT INTO unisql_employees (employee_id, employee_name, manager_id)
VALUES (1001, 'John Smith', 1003);
INSERT INTO unisql_employees (employee_id, employee_name, manager_id)
VALUES (1002, 'Jane Doe', 1003);
INSERT INTO unisql_employees (employee_id, employee_name, manager_id)
VALUES (1003, 'Mike Johnson', NULL);
-- 转换前Oracle SQL:
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM unisql_employees e, unisql_employees m WHERE e.manager_id = m.employee_id;
EMPLOYEE |MANAGER |
----------+------------+
John Smith|Mike Johnson|
Jane Doe |Mike Johnson|
-- 转换后GaussDB-Oracle SQL:
SELECT e.employee_name AS employee,m.employee_name AS manager FROM unisql_employees AS e CROSS JOIN unisql_employees AS m WHERE e.manager_id=m.employee_id
employee |manager |
----------+------------+
John Smith|Mike Johnson|
Jane Doe |Mike Johnson|
3.2.3.6.11. 笛卡尔积
如果联接查询中的两个表没有联接条件，返回其笛卡尔乘积，使用第一个表的每一行与另一表每一个行进行组合输出。笛卡尔乘积会生成许多行，但是大多是无用数据。例如，两个都有 100 行的表的笛卡尔积有 10,000 行，所以除非特别需要笛卡尔乘积，建议查询中始终包括一个联接条件以避免返回笛卡尔乘积。
示例
-- 创建 colors 表
CREATE TABLE unisql_colors (
color_id NUMBER,
color_name VARCHAR2(100)
);
-- 创建 sizes 表
CREATE TABLE unisql_sizes (
size_id NUMBER,
size_name VARCHAR2(100)
);
-- 插入示例数据
INSERT INTO unisql_colors (color_id, color_name)
VALUES (1, 'Red');
INSERT INTO unisql_colors (color_id, color_name)
VALUES (2, 'Blue');
INSERT INTO unisql_sizes (size_id, size_name)
VALUES (1, 'Small');
INSERT INTO unisql_sizes (size_id, size_name)
VALUES (2, 'Medium');
-- 转换前Oracle SQL:
SELECT c.color_id, c.color_name, s.size_id, s.size_name
FROM unisql_colors c, unisql_sizes s;
COLOR_ID|COLOR_NAME|SIZE_ID|SIZE_NAME|
--------+----------+-------+---------+
1|Red | 1|Small |
1|Red | 2|Medium |
2|Blue | 1|Small |
2|Blue | 2|Medium |
-- 转换后GaussDB-Oracle SQL:
SELECT c.color_id,c.color_name,s.size_id,s.size_name FROM unisql_colors AS c CROSS JOIN unisql_sizes AS s
color_id|color_name|size_id|size_name|
--------+----------+-------+---------+
1|Red | 1|Small |
1|Red | 2|Medium |
2|Blue | 1|Small |
2|Blue | 2|Medium |
3.2.3.6.12. INNER JOIN 内联接查询
内联接基于联接条件将两张表（例如 A 和 B）的列组合在一起，产生新的结果存放在表中。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足联接条件的组合。当满足联接条件时，表 A 和 B 中匹配的行会按列组合（并排组合）成结果集中的一行。联接产生的结果集等于首先对两张表做笛卡尔积，将表 A 中的每一行和表 B 中的每一行组合，然后返回满足联接条件的记录。
示例
-- 创建 unisql_orders 表
CREATE TABLE unisql_orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
);
-- 创建 unisql_customers 表
CREATE TABLE unisql_customers (
customer_id NUMBER,
customer_name VARCHAR2(100),
city VARCHAR2(100)
);
-- 插入示例数据
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000);
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500);
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (3, TO_DATE('2022-04-03', 'YYYY-MM-DD'), 1003, 1600);
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1001, 'John Smith', 'New York');
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1002, 'Jane Doe', 'Los Angeles');
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1004, 'Grace Miller', 'San Francisco');
-- 转换前Oracle SQL：
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
INNER JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
-- 转换后GaussDB-Oracle SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3.2.3.6.13. LEFT JOIN 左外联接查询
左联接（LEFT [OUTER] JOIN）中左表行未在右表匹配到行时，在右表自动填充 NULL。
示例
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
LEFT JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3|2022-04-03 00:00:00.000| | | 1600|
-- 转换后GaussDB-Oracle SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o LEFT JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3|2022-04-03 00:00:00.000| | | 1600|
3.2.3.6.14. RIGHT JOIN 右外联接查询
右联接（RIGHT [OUTER] JOIN）中右表行未在左表匹配到行时，在左表自动填充 NULL。
示例
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
RIGHT JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
| |Grace Miller |San Francisco| |
-- 转换后GaussDB-Oracle SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o RIGHT JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
| |Grace Miller |San Francisco| |
3.2.3.6.15. FULL JOIN 全外联接查询
全联接（FULL [OUTER] JOIN）左表或者右表未在其它表匹配到行时均会填充NULL。
示例
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
FULL JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
| |Grace Miller |San Francisco| |
3|2022-04-03 00:00:00.000| | | 1600|
-- 转换后GaussDB-Oracle SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o FULL JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
3|2022-04-03 00:00:00.000| | | 1600|
| |Grace Miller |San Francisco| |
3.2.3.6.16. 外联接符号（+）
对于左联接，在 WHERE 子句的联接条件中对右表的所有列应用外联接符号（+）；对于右联接，在 WHERE 子句的联接条件中对左表的所有列应用外联接符号（+）。
示例
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
UNISQL_TABLE1.*,
UNISQL_TABLE2.*
FROM
UNISQL_TABLE1,UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+);
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
2|B |BB| | | |
3|C |CC| | | |
-- 转换后GaussDB-Oracle SQL:
SELECT UNISQL_TABLE1.*,UNISQL_TABLE2.* FROM UNISQL_TABLE2 RIGHT JOIN UNISQL_TABLE1 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 WHERE 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
2|B |BB| | | |
3|C |CC| | | |
-- (+)左外连接多个条件
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
UNISQL_TABLE1.*,
UNISQL_TABLE2.*
FROM
UNISQL_TABLE1,
UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+)
AND UNISQL_TABLE1.c2 = UNISQL_TABLE2.c2(+);
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
2|B |BB| | | |
1|A |AA| | | |
3|C |CC| | | |
-- 转换后GaussDB-Oracle SQL:
SELECT UNISQL_TABLE1.*,UNISQL_TABLE2.* FROM UNISQL_TABLE2 RIGHT JOIN UNISQL_TABLE1 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 AND UNISQL_TABLE1.c2=UNISQL_TABLE2.c2 WHERE 1=1 AND 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
1|A |AA| | | |
2|B |BB| | | |
3|C |CC| | | |
-- (+)右外连接
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
*
FROM
UNISQL_TABLE1,UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1;
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM UNISQL_TABLE1 RIGHT JOIN UNISQL_TABLE2 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 WHERE 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
-- (+)右外连接多个条件
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
*
FROM
UNISQL_TABLE1,UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1
AND UNISQL_TABLE1.c2(+) = UNISQL_TABLE2.c2;
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
| | | 1|a |b |
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM UNISQL_TABLE1 RIGHT JOIN UNISQL_TABLE2 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 AND UNISQL_TABLE1.c2=UNISQL_TABLE2.c2 WHERE 1=1 AND 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
| | | 1|a |b |
-- (+)外连接在子查询中
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D');
-- 转换前Oracle SQL:
SELECT
*
FROM
(
SELECT t1.c1, t2.c2,t3.c3
FROM
UNISQL_TABLE1 t1,
UNISQL_TABLE2 t2,
UNISQL_TABLE3 t3
WHERE
t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
);
C1|C2|C3|
--+--+--+
1|a |D |
2| | |
3| | |
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM (SELECT t1.c1,t2.c2,t3.c3 FROM UNISQL_TABLE3 AS t3 RIGHT JOIN (UNISQL_TABLE2 AS t2 RIGHT JOIN UNISQL_TABLE1 AS t1 ON t1.c1=t2.c1) ON t1.c1=t3.c1 WHERE 1=1 AND 1=1) AS uni_sub
c1|c2|c3|
--+--+--+
1|a |D |
2| | |
3| | |
-- (+)外连接加笛卡尔积
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D');
-- 转换前Oracle SQL:
SELECT
t1.*,
t2.*,
t3.*
FROM
UNISQL_TABLE1 t1,
UNISQL_TABLE2 t2,
UNISQL_TABLE3 t3
WHERE
t1.c1(+) = t2.c1
AND t1.c1 = t3.c1;
C1|C2|C3|C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+--+--+--+
1|A |AA| 1|a |b | 1|C |D |
-- 转换后GaussDB-Oracle SQL:
SELECT t1.*,t2.*,t3.* FROM (UNISQL_TABLE1 AS t1 RIGHT JOIN UNISQL_TABLE2 AS t2 ON t1.c1=t2.c1) CROSS JOIN UNISQL_TABLE3 AS t3 WHERE 1=1 AND t1.c1=t3.c1
c1|c2|c3|c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+--+--+--+
1|A |AA| 1|a |b | 1|C |D |
3.2.3.6.16.1. WITH CLAUSE
如果查询语句中有多个相同的子查询，可以把相同的子查询放在 WITH CLAUSE 中作为公共表达式，在主体查询中直接引用即可。
语法
with_clause_select:
with_clause simple_select
with_clause:
WITH query_table_name [opt_column_alias_name_list] AS ( select_clause )
select_clause:
simple_select | select_clause_set
opt_column_alias_name_list:
(column_name_list)
column_name_list:
column_name [,column_name...]
示例
CREATE TABLE unisql_tbl1(col1 INT,col2 INT,col3 INT);
INSERT INTO unisql_tbl1 VALUES(1,1,1);
INSERT INTO unisql_tbl1 VALUES(2,2,2);
INSERT INTO unisql_tbl1 VALUES(3,3,3);
-- 转换前Oracle SQL:
-- 直接使用表 unisql_tbl1 的列名作为 u_tbl1 的列名
WITH u_tbl1 AS(SELECT * FROM unisql_tbl1) SELECT * FROM u_tbl1;
COL1|COL2|COL3|
----+----+----+
1| 1| 1|
2| 2| 2|
3| 3| 3|
-- 转换后GaussDB-Oracle SQL:
WITH u_tbl1 AS (SELECT * FROM unisql_tbl1) SELECT * FROM u_tbl1
col1|col2|col3|
----+----+----+
1| 1| 1|
2| 2| 2|
3| 3| 3|
-- 转换前Oracle SQL:
-- 直接给 w_tbl1 命名列名
WITH w_tbl1(c1,c2,c3) AS(SELECT * FROM unisql_tbl1) SELECT * FROM w_tbl1;
C1|C2|C3|
--+--+--+
1| 1| 1|
2| 2| 2|
3| 3| 3|
-- 转换后GaussDB-Oracle SQL:
WITH w_tbl1 (c1, c2, c3) AS (SELECT * FROM unisql_tbl1) SELECT * FROM w_tbl1
c1|c2|c3|
--+--+--+
1| 1| 1|
2| 2| 2|
3| 3| 3|
3.2.3.6.16.2. 子查询
子查询指的是 SELECT 查询语句中嵌套了另一个或者多个 SELECT 语句，可以返回单行结果、多行结果或不返回结果。SELECT 语句的 FROM 子句中的子查询也称为内联视图。可以在嵌入式视图中嵌套任意数量的子查询。SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。
子查询可以分为相关子查询和非相关子查询。相关子查询指该子查询的执行依赖了外部查询的变量，这种子查询通常会执行多次。非相关子查询指该子查询的执行不依赖外部查询的变量，这种子查询一般只需要计算一次。对于非相关子查询与部分相关子查询，可以通过改写进行子查询消除，实现嵌套子查询的展开。
语法
SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
FROM { table_reference | join_clause | ( join_clause ) }
[ , { table_reference | join_clause | (join_clause) } ]
[ where_clause ]
[ hierarchical_query_clause ]
[ group_by_clause ]
| subquery { UNION [ALL] | INTERSECT | MINUS } subquery [ { UNION [ALL] | INTERSECT | MINUS } subquery ]
| ( subquery ) [ order_by_clause ]
示例
-- 创建表结构
CREATE TABLE unisql_table_a(PK INT, name VARCHAR(25));
INSERT INTO unisql_table_a VALUES(1,'福克斯');
INSERT INTO unisql_table_a VALUES(2,'警察');
INSERT INTO unisql_table_a VALUES(3,'的士');
INSERT INTO unisql_table_a VALUES(4,'林肯');
INSERT INTO unisql_table_a VALUES(5,'亚利桑那州');
INSERT INTO unisql_table_a VALUES(6,'华盛顿');
INSERT INTO unisql_table_a VALUES(7,'戴尔');
INSERT INTO unisql_table_a VALUES(10,'朗讯');
CREATE TABLE unisql_table_b(PK INT, name VARCHAR(25));
INSERT INTO unisql_table_b VALUES(1,'福克斯');
INSERT INTO unisql_table_b VALUES(2,'警察');
INSERT INTO unisql_table_b VALUES(3,'的士');
INSERT INTO unisql_table_b VALUES(6,'华盛顿');
INSERT INTO unisql_table_b VALUES(7,'戴尔');
INSERT INTO unisql_table_b VALUES(8,'微软');
INSERT INTO unisql_table_b VALUES(9,'苹果');
INSERT INTO unisql_table_b VALUES(11,'苏格兰威士忌');
-- 转换前Oracle SQL:
-- 没有依赖关系的子查询
SELECT * FROM unisql_table_a T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b T2);
PK|NAME|
--+----+
1|福克斯 |
2|警察 |
3|的士 |
6|华盛顿 |
7|戴尔 |
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM unisql_table_a AS T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b AS T2)
pk|name|
--+----+
1|福克斯 |
2|警察 |
3|的士 |
6|华盛顿 |
7|戴尔 |
-- 转换前Oracle SQL:
-- 有依赖关系的子查询，子查询中用到了外层查询变量 T1.PK
SELECT * FROM unisql_table_a T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b T2 WHERE T2.PK = T1.PK);
PK|NAME|
--+----+
1|福克斯 |
2|警察 |
3|的士 |
6|华盛顿 |
7|戴尔 |
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM unisql_table_a AS T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b AS T2 WHERE T2.PK=T1.PK)
pk|name|
--+----+
1|福克斯 |
2|警察 |
3|的士 |
6|华盛顿 |
7|戴尔 |
3.2.3.6.16.3. DUAL 表查询
DUAL 表有一个列 DUMMY，定义为 VARCHAR2(1)，并包含一个值为 X 的行。在使用 SELECT 语句计算常量表达式时，可以从 DUAL 表中选择数据。
-- 转换前Oracle SQL:
SELECT 1 , 2 + 1,NULL, '' FROM dual;
1|2+1|NULL|''|
-+---+----+--+
1| 3| | |
-- 转换后GaussDB-Oracle SQL:
SELECT 1,2+1,NULL,'' FROM sys_dummy
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
1| 3| | |
警告
Oracle 的DUAL表位于用户SYS的Schema 中，所有用户都可以通过名称 DUAL 访问该表。与之相对应的，GuassDB-Oracle的sys_dummy表位于用户 sys 的Schema 中。 当指定非SYS的Schema的DUAL表，统一SQL只作原模原样的转化。 对于select DUAL.dummy from DUAL 这种情况，只会改写 from 后面的表名称，改写后为 select DUAL.dummy from sys_dummy。
3.2.3.6.16.4. ORDER BY 子句
ORDER BY 子句用于对查询结果按照一个或多个属性列进行升序（ASC）或降序（DESC）排列，默认为升序。排序查询的 SQL 语句格式如下：
ORDER BY expr
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, expr
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
]
示例
DROP TABLE orderby_nulls_test;
CREATE TABLE orderby_nulls_test(id int,name varchar(10));
INSERT INTO orderby_nulls_test values(1,'jack');
INSERT INTO orderby_nulls_test values(2,'tom');
INSERT INTO orderby_nulls_test values(3,'cacy');
INSERT INTO orderby_nulls_test values(4,'bob');
INSERT INTO orderby_nulls_test values(null,'nina');
-- 转换前Oracle SQL:
SELECT * FROM orderby_nulls_test ORDER BY id ASC;
SELECT * FROM orderby_nulls_test ORDER BY id DESC;
SELECT * FROM orderby_nulls_test ORDER BY 1 NULLS FIRST;
SELECT * FROM orderby_nulls_test ORDER BY id DESC NULLS FIRST;
SELECT id,name FROM orderby_nulls_test UNION SELECT id,name FROM orderby_nulls_test ORDER BY id NULLS FIRST;
SELECT e.id, e.name FROM orderby_nulls_test e LEFT JOIN orderby_nulls_test d ON e.id = d.id ORDER BY d.id ASC NULLS FIRST;
SELECT id,name,ROW_NUMBER() over(PARTITION BY id ORDER BY name NULLS last) FROM orderby_nulls_test;
SELECT id,name,MAX(id) over(PARTITION BY id ORDER BY name NULLS last) FROM orderby_nulls_test;
SELECT id, COUNT(*) AS ct FROM orderby_nulls_test GROUP BY id ORDER BY COUNT(*) NULLS FIRST;
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM orderby_nulls_test ORDER BY id
SELECT * FROM orderby_nulls_test ORDER BY id DESC
SELECT * FROM orderby_nulls_test ORDER BY 1 NULLS FIRST
SELECT * FROM orderby_nulls_test ORDER BY id DESC NULLS FIRST
SELECT id,name FROM orderby_nulls_test UNION SELECT id,name FROM orderby_nulls_test ORDER BY id NULLS FIRST
SELECT e.id,e.name FROM orderby_nulls_test AS e LEFT JOIN orderby_nulls_test AS d ON e.id=d.id ORDER BY d.id NULLS FIRST
SELECT id,name,ROW_NUMBER() OVER (PARTITION BY id ORDER BY name NULLS LAST) FROM orderby_nulls_test
SELECT id,name,MAX(id) OVER (PARTITION BY id ORDER BY name NULLS LAST) FROM orderby_nulls_test
SELECT id,COUNT(1) AS ct FROM orderby_nulls_test GROUP BY id ORDER BY COUNT(1) NULLS FIRST
3.2.3.6.16.5. AS作为别名
通常AS在数据库中作为保留关键字，一般不建议作为字段的别名，但是有时候开发人员在编写SQL语句时，存在使用AS作为别名的情况。 统一SQL支持AS作为别名时到目标库的转换。
示例
-- 转换前Oracle SQL:
SELECT 0 as, 1 as,2 "as", 3 a, 4 as b, count(*) as FROM dual;
0AS|1AS|as|A|B|COUNT(*)AS|
---+---+--+-+-+----------+
0| 1| 2|3|4| 1|
-- 转换后GaussDB-Oracle SQL:
SELECT 0 AS as,1 AS as,2 AS "as",3 AS a,4 AS b,count(1) AS as
as|as|as|a|b|as|
--+--+--+-+-+--+
0| 1| 2|3|4| 1|