4.2.3.2. PostgreSQL
4.2.3.2.1. 简单查询
简单查询是指从数据库的一个或多个选择列表或视图中检索一个或多个列数据，列的数量以及它们的数据类型和长度由选择列表的元素确定。而选择列表指的是
SELECT关键字之后和FROM子句之前的表达式列表。如果两个或多个表有一些共同的列名，则必须用表名限定列名。您可以使用列别名
col_alias来标记紧接在选择列表之前的表达式。别名可以在ORDER BY子句中使用，但不能在查询中的其他子句中使用。查询的
SELECT语法如下：
simple_select:
SELECT [ DISTINCT | ALL] select_expr_list
[FROM from_list ]
[WHERE condition]
[GROUP BY group_expression_list [HAVING condition]]
[ORDER BY order_expression_list]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
[row_limiting_clause]
select_expr_list:
table_name.*
| table_alias_name.*
| expr [[AS] column_alias_name]
from_list:
DUAL
| table_reference [, table_reference...]
table_reference:
simple_table
| joined_table
| pivot_clause
| unpivot_clause
| table_name@dblink_name
simple_table:
table_name [partition_option] [table_alias_name]
| (select_stmt) [table_alias_name]
| (table_reference_list)
joined_table:
table_reference [INNER] JOIN simple_table [join_condition]
| table_reference outer_join_type JOIN simple_table join_condition
partition_option:
PARTITION (partition_name)
outer_join_type:
{LEFT | RIGHT | FULL} [OUTER]
join_condition:
ON expression
condition:
expression
group_expression_list:
group_expression [, group_expression...]
group_expression:
expression
order_expression_list:
order_expression [, order_expression...]
order_expression:
expression [ASC | DESC]
row_limiting_clause:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount } ] { ROW | ROWS } { ONLY } ]
pivot_clause：
PIVOT
(aggregate_function ( expr ) [[AS] alias ]
[, aggregate_function ( expr ) [[AS] alias ]... ]
pivot_for_clause
pivot_in_clause
)
pivot_for_clause：
FOR { column }
pivot_in_clause
IN
( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
[, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
)
unpivot_clause ：
UNPIVOT
( { column }
pivot_for_clause
unpivot_in_clause
)
unpivot_in_clause：
IN
( { column | ( column [, column... ]) }
)
CREATE TABLE unisql_simple_query_employee (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
manager_id INT,
salary NUMERIC
);
INSERT INTO unisql_simple_query_employee VALUES(1, 'DEL', 'FA BEN', 1, 1500);
INSERT INTO unisql_simple_query_employee VALUES(2, 'AXEL', 'BELL', 1, 1000);
INSERT INTO unisql_simple_query_employee VALUES(3, 'CRIS', 'RACHAR', 1, 1000);
INSERT INTO unisql_simple_query_employee VALUES(4, 'BlAIR', 'WALDORF', 1, 800);
-- 转换前Oracle SQL：
-- 查询表中部分列
SELECT first_name, last_name, salary FROM unisql_simple_query_employee;
-- 查询表中所有列
SELECT * FROM unisql_simple_query_employee;
-- 对表中的列进行数学运算
SELECT salary+100 FROM unisql_simple_query_employee;
-- 给表中的列取别名
SELECT salary*12 AS year_salary FROM unisql_simple_query_employee;
-- 将表中的数据去重
SELECT DISTINCT manager_id FROM unisql_simple_query_employee;
-- 使用 CASE WHEN 语句查询表中的数据
SELECT salary, CASE WHEN salary >= 1200 then 'high' WHEN salary >= 1000 THEN 'middle' ELSE 'low' END AS type FROM unisql_simple_query_employee;
-- 转换后PostgreSQL SQL：
-- 查询表中部分列
SELECT first_name,last_name,salary FROM unisql_simple_query_employee
-- 查询表中所有列
SELECT * FROM unisql_simple_query_employee
-- 对表中的列进行数学运算
SELECT salary+100 FROM unisql_simple_query_employee
-- 给表中的列取别名
SELECT salary*12 AS year_salary FROM unisql_simple_query_employee
-- 将表中的数据去重
SELECT DISTINCT manager_id FROM unisql_simple_query_employee
-- 使用 CASE WHEN 语句查询表中的数据
SELECT salary,CASE WHEN salary>=1200 THEN 'high' WHEN salary>=1000 THEN 'middle' ELSE 'low' END AS type FROM unisql_simple_query_employee
4.2.3.2.1.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|
-- 转换后PostgreSQL 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|
-- 转换后PostgreSQL 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|
4.2.3.2.1.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|
-- 转换后PostgreSQL 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|
4.2.3.2.1.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|
-- 转换后PostgreSQL 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|
4.2.3.2.2. 层次查询
层次查询（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
4.2.3.2.2.1. 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 |
-- 转换后PostgreSQL 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 |
-- 转换后PostgreSQL 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|
-- 转换后PostgreSQL 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|
4.2.3.2.3. 复合查询
使用集合运算符 UNION、UNION ALL、INTERSECT 和 MINUS 来组合多个查询，即复合查询。所有集合运算符都具有相同的优先级。
4.2.3.2.3.1. 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|
-- 转换后PostgreSQL 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|
4.2.3.2.3.2. 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|
-- 转换后PostgreSQL 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|
4.2.3.2.3.3. 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|
-- 转换后PostgreSQL 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|
4.2.3.2.3.4. 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|
-- 转换后PostgreSQL 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|
4.2.3.2.4. 联接查询
联接（Join）是将两个或多个表、视图的结合在一起的查询。
4.2.3.2.4.1. 等值联接
等值联接是包含等值运算符的联接。在进行等值联接时将指定列满足等值条件的行进行组合输出。
示例
-- 创建 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|
-- 转换后PostgreSQL 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|
4.2.3.2.4.2. 自联接
自联接是表与其自身的联接。该表在 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|
-- 转换后PostgreSQL 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|
4.2.3.2.4.3. 笛卡尔积
如果联接查询中的两个表没有联接条件，返回其笛卡尔乘积，使用第一个表的每一行与另一表每一个行进行组合输出。笛卡尔乘积会生成许多行，但是大多是无用数据。例如，两个都有 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 |
-- 转换后PostgreSQL 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 |
4.2.3.2.4.4. 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|
-- 转换后PostgreSQL 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|
4.2.3.2.4.5. 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|
-- 转换后PostgreSQL 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|
4.2.3.2.4.6. 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| |
-- 转换后PostgreSQL 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| |
4.2.3.2.4.7. 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|
-- 转换后PostgreSQL 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| |
4.2.3.2.4.8. 外联接符号（+）
对于左联接，在 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| | | |
-- 转换后PostgreSQL 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| | | |
-- 转换后PostgreSQL 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 |
-- 转换后PostgreSQL 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 |
-- 转换后PostgreSQL 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| | |
-- 转换后PostgreSQL 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 |
-- 转换后PostgreSQL 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 |
4.2.3.2.5. 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|
-- 转换后PostgreSQL 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|
-- 转换后PostgreSQL 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|
4.2.3.2.6. 子查询
子查询指的是 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|戴尔 |
-- 转换后PostgreSQL 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|戴尔 |
-- 转换后PostgreSQL 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|戴尔 |
4.2.3.2.7. DUAL 表查询
DUAL 表有一个列 DUMMY，定义为 VARCHAR2(1)，并包含一个值为 X 的行。在使用 SELECT 语句计算常量表达式时，可以从 DUAL 表中选择数据。
-- 转换前Oracle SQL:
SELECT
1,
2 + 1,
NULL,
''
FROM
dual;
1|2+1|NULL|''|
-+---+----+--+
1| 3| | |
-- 转换后PostgreSQL SQL:
SELECT 1,2+1,NULL,''
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
1| 3| | |
4.2.3.2.8. 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;
-- 转换后PostgreSQL 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
4.2.3.2.9. 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|
-- 转换后PostgreSQL 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|