Oracle与PostgreSQL子查询有什么不同

本篇内容主要讲解"Oracle与PostgreSQL子查询有什么不同"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"Oracle与PostgreSQL子查询有什么不同"吧!

准确的表达应该是在子查询的having条件中出现agg函数且依赖父查询的相关字段时，Oracle支持而PG不支持。

Oracle
创建表，插入数据，执行查询，OK！

TEST-orcl@DESKTOP-V430TU3>drop table tbl1;Table dropped.TEST-orcl@DESKTOP-V430TU3>drop table tbl2;Table dropped.TEST-orcl@DESKTOP-V430TU3>drop table tbl3;Table dropped.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>create table tbl1 (id int,c1 int,c2 int,c3 int);Table created.TEST-orcl@DESKTOP-V430TU3>create table tbl2 (id int,c1 int,c2 int,c3 int);Table created.TEST-orcl@DESKTOP-V430TU3>create table tbl3 (id int,c1 int,c2 int,c3 int);Table created.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>insert into tbl1 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl1 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl1 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl1 values(2,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl1 values(2,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl1 values(3,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>insert into tbl2 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl2 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl2 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl2 values(2,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl2 values(2,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl2 values(3,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>truncate table tbl3;Table truncated.TEST-orcl@DESKTOP-V430TU3>insert into tbl3 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into tbl3 values(1,1,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>commit;Commit complete.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>truncate table tbl3;Table truncated.TEST-orcl@DESKTOP-V430TU3>insert into tbl3 values(1,2,1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>select a.id,sum(a.c1) as sum_c1,sum(a.c2) as sum_c2  2  from tbl1 a,tbl2 b  3  where a.id = b.id  4    and exists (select 1 from tbl3 c where c.id = a.id group by c.id having sum(c.c1) > sum(a.c1))  5  group by a.id;        ID     SUM_C1     SUM_C2---------- ---------- ----------         1          9          9TEST-orcl@DESKTOP-V430TU3

不过，就算Oracle支持这样的写法，也不建议这样来写，原因是SQL语义理解起来并不友好，难以理解。

PG
创建表，插入数据，执行查询，出错。

[pg12@localhost ~]$ psqlExpanded display is used automatically.psql (12.1)Type "help" for help.[local:/data/run/pg12]:5120 pg12@testdb=# drop table tbl1;s(1,1,1,1);insert into tbl1 values(1,1,1,1);insert into tbl2 select * from tbl1;insert into tbl2 select * from tbl1;insert into tbl3 select * from tbl1;commit;ERROR:  table "tbl1" does not exist[local:/data/run/pg12]:5120 pg12@testdb=# drop table tbl2;ERROR:  table "tbl2" does not exist[local:/data/run/pg12]:5120 pg12@testdb=# drop table tbl3;ERROR:  table "tbl3" does not exist[local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# create table tbl1 (id int,c1 int,c2 int,c3 int);CREATE TABLE[local:/data/run/pg12]:5120 pg12@testdb=# create table tbl2 (id int,c1 int,c2 int,c3 int);CREATE TABLE[local:/data/run/pg12]:5120 pg12@testdb=# create table tbl3 (id int,c1 int,c2 int,c3 int);CREATE TABLE[local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# insert into tbl1 values(1,1,1,1);INSERT 0 1[local:/data/run/pg12]:5120 pg12@testdb=# insert into tbl1 values(1,1,1,1);INSERT 0 1[local:/data/run/pg12]:5120 pg12@testdb=# insert into tbl1 values(1,1,1,1);INSERT 0 1[local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# insert into tbl2 select * from tbl1;INSERT 0 3[local:/data/run/pg12]:5120 pg12@testdb=# insert into tbl2 select * from tbl1;INSERT 0 3[local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# insert into tbl3 select * from tbl1;INSERT 0 3[local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# commit;WARNING:  there is no transaction in progressCOMMIT[local:/data/run/pg12]:5120 pg12@testdb=# select a.id,sum(a.c1) as sum_c1,sum(a.c2) as sum_c2pg12@testdb-# from tbl1 a,tbl2 bpg12@testdb-# where a.id = b.idpg12@testdb-#   and exists (select 1 from tbl3 c where c.id = a.id group by c.id having sum(c.c1)  = sum(a.c1))pg12@testdb-# group by a.id;ERROR:  aggregate functions are not allowed in WHERELINE 4: ...ere c.id = a.id group by c.id having sum(c.c1)  = sum(a.c1))                                                             ^[local:/data/run/pg12]:5120 pg12@testdb=#

出现的错误是"aggregate functions are not allowed in WHERE"，但条件明明在having怎么报WHERE中出现agg函数呢？原因是PG认为条件sum(c.c1) = sum(a.c1)中的a.c1出现在父查询中，该条件认为是WHERE中的条件。

到此，相信大家对"Oracle与PostgreSQL子查询有什么不同"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！