您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“Oracle與PostgreSQL子查詢有什么不同”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學習“Oracle與PostgreSQL子查詢有什么不同”吧!
準確的表達應該是在子查詢的having條件中出現(xiàn)agg函數(shù)且依賴父查詢的相關字段時,Oracle支持而PG不支持。
Oracle
創(chuàng)建表,插入數(shù)據(jù),執(zhí)行查詢,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 9 TEST-orcl@DESKTOP-V430TU3
不過,就算Oracle支持這樣的寫法,也不建議這樣來寫,原因是SQL語義理解起來并不友好,難以理解。
PG
創(chuàng)建表,插入數(shù)據(jù),執(zhí)行查詢,出錯。
[pg12@localhost ~]$ psql Expanded 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 progress COMMIT [local:/data/run/pg12]:5120 pg12@testdb=# select a.id,sum(a.c1) as sum_c1,sum(a.c2) as sum_c2 pg12@testdb-# from tbl1 a,tbl2 b pg12@testdb-# where a.id = b.id pg12@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 WHERE LINE 4: ...ere c.id = a.id group by c.id having sum(c.c1) = sum(a.c1)) ^ [local:/data/run/pg12]:5120 pg12@testdb=#
出現(xiàn)的錯誤是“aggregate functions are not allowed in WHERE”,但條件明明在having怎么報WHERE中出現(xiàn)agg函數(shù)呢?原因是PG認為條件sum(c.c1) = sum(a.c1)中的a.c1出現(xiàn)在父查詢中,該條件認為是WHERE中的條件。
到此,相信大家對“Oracle與PostgreSQL子查詢有什么不同”有了更深的了解,不妨來實際操作一番吧!這里是億速云網(wǎng)站,更多相關內(nèi)容可以進入相關頻道進行查詢,關注我們,繼續(xù)學習!
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。