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

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

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

TEST-orcl@DESKTOP-V430TU3>droptabletbl1;Tabledropped.TEST-orcl@DESKTOP-V430TU3>droptabletbl2;Tabledropped.TEST-orcl@DESKTOP-V430TU3>droptabletbl3;Tabledropped.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>createtabletbl1(idint,c1int,c2int,c3int);Tablecreated.TEST-orcl@DESKTOP-V430TU3>createtabletbl2(idint,c1int,c2int,c3int);Tablecreated.TEST-orcl@DESKTOP-V430TU3>createtabletbl3(idint,c1int,c2int,c3int);Tablecreated.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>insertintotbl1values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl1values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl1values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl1values(2,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl1values(2,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl1values(3,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>insertintotbl2values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl2values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl2values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl2values(2,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl2values(2,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl2values(3,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>truncatetabletbl3;Tabletruncated.TEST-orcl@DESKTOP-V430TU3>insertintotbl3values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl3values(1,1,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>commit;Commitcomplete.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>truncatetabletbl3;Tabletruncated.TEST-orcl@DESKTOP-V430TU3>insertintotbl3values(1,2,1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>selecta.id,sum(a.c1)assum_c1,sum(a.c2)assum_c22fromtbl1a,tbl2b3wherea.id=b.id4andexists(select1fromtbl3cwherec.id=a.idgroupbyc.idhavingsum(c.c1)>sum(a.c1))5groupbya.id;IDSUM_C1SUM_C2------------------------------199TEST-orcl@DESKTOP-V430TU3

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

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

[pg12@localhost~]$psqlExpandeddisplayisusedautomatically.psql(12.1)Type"help"forhelp.[local:/data/run/pg12]:5120pg12@testdb=#droptabletbl1;s(1,1,1,1);insertintotbl1values(1,1,1,1);insertintotbl2select*fromtbl1;insertintotbl2select*fromtbl1;insertintotbl3select*fromtbl1;commit;ERROR:table"tbl1"doesnotexist[local:/data/run/pg12]:5120pg12@testdb=#droptabletbl2;ERROR:table"tbl2"doesnotexist[local:/data/run/pg12]:5120pg12@testdb=#droptabletbl3;ERROR:table"tbl3"doesnotexist[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#createtabletbl1(idint,c1int,c2int,c3int);CREATETABLE[local:/data/run/pg12]:5120pg12@testdb=#createtabletbl2(idint,c1int,c2int,c3int);CREATETABLE[local:/data/run/pg12]:5120pg12@testdb=#createtabletbl3(idint,c1int,c2int,c3int);CREATETABLE[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#insertintotbl1values(1,1,1,1);INSERT01[local:/data/run/pg12]:5120pg12@testdb=#insertintotbl1values(1,1,1,1);INSERT01[local:/data/run/pg12]:5120pg12@testdb=#insertintotbl1values(1,1,1,1);INSERT01[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#insertintotbl2select*fromtbl1;INSERT03[local:/data/run/pg12]:5120pg12@testdb=#insertintotbl2select*fromtbl1;INSERT03[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#insertintotbl3select*fromtbl1;INSERT03[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#commit;WARNING:thereisnotransactioninprogressCOMMIT[local:/data/run/pg12]:5120pg12@testdb=#selecta.id,sum(a.c1)assum_c1,sum(a.c2)assum_c2pg12@testdb-#fromtbl1a,tbl2bpg12@testdb-#wherea.id=b.idpg12@testdb-#andexists(select1fromtbl3cwherec.id=a.idgroupbyc.idhavingsum(c.c1)=sum(a.c1))pg12@testdb-#groupbya.id;ERROR:aggregatefunctionsarenotallowedinWHERELINE4:...erec.id=a.idgroupbyc.idhavingsum(c.c1)=sum(a.c1))^[local:/data/run/pg12]:5120pg12@testdb=#

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

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