作為去O實力派,沒辦法只能安排。
由于開發的表比較復雜,我們這里來造一個cats表,說明一下這個問題。我們先看看Oracle中的查詢結果。
create table hbdx_zhaoyou.cats( catno number(4,0), catname varchar2(15), job varchar2(15), mgr number(4,0), constraint pk_emp primary key (catno) ); insert into hbdx_zhaoyou.cats values (0, king, ceo, null ); insert into hbdx_zhaoyou.cats values (1, jones, cio, 0); insert into hbdx_zhaoyou.cats values (2, blake, cfo, 0); insert into hbdx_zhaoyou.cats values (3, clark, hr exec, 0); insert into hbdx_zhaoyou.cats values (4, scott, it mgr, 1); insert into hbdx_zhaoyou.cats values (5, turner, architect,1); insert into hbdx_zhaoyou.cats values (6, adams, fin.mgr,2 ); insert into hbdx_zhaoyou.cats values (7, james, hr.mgr,3); insert into hbdx_zhaoyou.cats values (8, ford, it support,4); insert into hbdx_zhaoyou.cats values (9, miller, developer, 4); insert into hbdx_zhaoyou.cats values (10, smith, accountant,6); insert into hbdx_zhaoyou.cats values (11, allen, payroll clerk,6); insert into hbdx_zhaoyou.cats values (12, ward, hr officer,7 ); commit; |
我們在Oracle中執行層次查詢得到下列結果。
SQL> SELECT catno,catname,job,level FROM hbdx_zhaoyou.cats CONNECT BY PRIOR catno = mgr START WITH mgr IS NULL order by level ; CATNO CATNAME JOB LEVEL ---------- --------------- --------------- ---------- 0 king ceo 1 1 jones cio 2 2 blake cfo 2 3 clark hr exec 2 6 adams fin.mgr 3 7 james hr.mgr 3 5 turner architect 3 4 scott it mgr 3 8 ford it support 4 11 allen payroll clerk 4 12 ward hr officer 4 9 miller developer 4 10 smith accountant 4 |
類似于上圖,我們知道King是老大,他的下面一級是經理級別,一共有三個經理,分別是jones、blake、clark,然后以此往下推。
這里Oracle的語法:
CONNECT BY :定義了父級別和子級別之間的關系。
PRIOR:則指定了父級
START WITH:定義我們希望查詢開始的記錄。
level:指示層次結構級別的偽列。
那么在PostgreSQL中有兩種實現的方法,第一種叫CTE(commontable expressions),簡稱公用表表達式。第二種是安裝自帶插件,使用PG的connectby函數。
CTE又叫commontable expressions,它隸屬于SQL:1999標準,在Oracle11gR2版本、MySQL8.0版本、PostgreSQL9.4以上版本都支持。因為是標準的語法,我們這里用mariadb官方文檔(最容易懂)的圖來說明一下原理。
首選我們要使用recursive關鍵字來表示,這是一個遞歸的CTE(公用表達式)。然后第一部分叫AnchorPart,翻譯過來就是錨點。這個錨點我覺得就代表了樹形查詢的一個展開的點,比如你要從我們CATS表的ITMGR這個節點查詢,那么這個條件就是錨點。
接下來就是Recursivepart,遞歸的部分。這里會告訴我們每個遞歸的步驟將要做什么。每次執行出來的結果,就會存放到結果表中,一直到整個遞歸結束。
下面的圖詳細的說明了遞歸到結果集這一過程。
首選查詢錨點,取出name=‘Alex’的記錄放入到結果表。
通過錨點查詢的數據和原來的數據表關聯,查詢出錨點的下一層數據。比如這里查的是Alex的father和mother。
這里將上面查出來的數據Dad和Mom存到結果表中。
接下來繼續根據上面查詢的Dad和Mom,繼續查詢他們的father和mother。
這里將上面查出來的數據GrandpaBill存到結果表中
就這樣一直查,直到沒有結果為止。
上面的原理和語法介紹完了,我們可以在PG中寫同樣的SQL實現上述Oracle中connectby功能。
with recursive cte as ( select catno, catname, mgr from cats where mgr is null union all select e.catno, e.catname, e.mgr from cte c join cats e on e.mgr = c.catno ) select * from cte; catno | catname | mgr -------+---------+----- 0 | king | 1 | jones | 0 2 | blake | 0 3 | clark | 0 4 | scott | 1 5 | turner | 1 6 | adams | 2 7 | james | 3 8 | ford | 4 9 | miller | 4 10 | smith | 6 11 | allen | 6 12 | ward | 7 |
可以看到結果類似,但是缺少像Oracle中的偽列level。這個偽列是需要我們自己構造一個的。
with recursive cte as ( select catno, catname, mgr,1 AS level from cats where mgr is null union all select e.catno, e.catname, e.mgr,c.level + 1 from cte c join cats e on e.mgr = c.catno ) select * from cte; catno | catname | mgr | level -------+---------+-----+------- 0 | king | | 1 1 | jones | 0 | 2 2 | blake | 0 | 2 3 | clark | 0 | 2 4 | scott | 1 | 3 5 | turner | 1 | 3 6 | adams | 2 | 3 7 | james | 3 | 3 8 | ford | 4 | 4 9 | miller | 4 | 4 10 | smith | 6 | 4 11 | allen | 6 | 4 12 | ward | 7 | 4 |
手動增加了一個列,就可以把level偽列功能實現了,至此我們就解決了開發的問題。
上面介紹了比較標準的CTE表達式,是在各種數據庫都已經兼容的語法。而PG也有他自己獨有的一種方法叫connectby,PG的connectby和Oracle中的connectby使用方式有很大的不同。
首先我們要安裝插件tablefunc,這個插件是軟件自帶的。安裝很簡單直接createextension tablefunc就可以了。
這個插件有很多功能,我們這里只用最后一個功能connectby。
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ]) |
relname | 源表的名稱 |
keyid_fld | 關鍵字段 |
parent_keyid_fld | 父鍵的關鍵字段 |
orderby_fld | 排序同級別字段(可選) |
start_with | 起始行的鍵值 |
max_depth | 要向下展開的最大深度,零表示無限深度 |
branch_delim | 在分支輸出中用于分隔鍵值的字符串(可 選 |
接下來就是見證這個函數魅力的時候了。
SELECT * FROM connectby(cats, catno, mgr, 0, 0, ->) AS t(keyid numeric, parent_keyid numeric, level int, branch text) order by level asc; keyid | parent_keyid | level | branch -------+--------------+-------+------------- 0 | | 0 | 0 1 | 0 | 1 | 0->1 2 | 0 | 1 | 0->2 3 | 0 | 1 | 0->3 4 | 1 | 2 | 0->1->4 6 | 2 | 2 | 0->2->6 7 | 3 | 2 | 0->3->7 5 | 1 | 2 | 0->1->5 9 | 4 | 3 | 0->1->4->9 8 | 4 | 3 | 0->1->4->8 10 | 6 | 3 | 0->2->6->10 11 | 6 | 3 | 0->2->6->11 12 | 7 | 3 | 0->3->7->12 |
可以看到使用起來非常方便,直接就輸入參數就行了。當然這里有一個問題是我們沒辦法通過connectby函數展示全部的列,它的函數參數寫死了類型。如果我們要展示cats表中的catname,就需要我們把當前結果集和原表cats在做一次關聯。
select keyid,catname,parent_keyid,level+1 from connectby(cats, catno, mgr, 0, 0, ->) AS t(keyid numeric, parent_keyid numeric, level int, branch text) inner join cats on catno = keyid order by level,mgr asc; keyid | catname | parent_keyid | ?column? -------+---------+--------------+---------- 0 | king | | 1 1 | jones | 0 | 2 2 | blake | 0 | 2 3 | clark | 0 | 2 5 | turner | 1 | 3 4 | scott | 1 | 3 6 | adams | 2 | 3 7 | james | 3 | 3 9 | miller | 4 | 4 8 | ford | 4 | 4 10 | smith | 6 | 4 11 | allen | 6 | 4 12 | ward | 7 | 4 |
那么這么寫就和我前面的withrecursive的結果集完全一致了。
如果拿我們想要的結果來看的話。通過執行計劃來看,很明顯是CTE快一些。
就算我們不取catname,取消掉關聯,connecyby函數執行的效率也是比CTE低的。
終于有人喊出了口號:
CONNECTBY Is Dead,
LongLive CTE!
參考文檔:
RecursiveCommon Table Expressions Overview
https://mariadb.com/kb/en/recursive-common-table-expressions-overview/
Hierarchicaland recursive queries in SQL
https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression
F.38.tablefunc
https://www.postgresql.org/docs/12/tablefunc.html
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/130088.html
摘要:但如果涉及到跨大版本升級比如升級到,這種直接替換軟件就不行了,因為跨版本的內部存儲形式發生了變化官方對于升級提供了種方法,這里遷移我使用了來進行處理細節可以參考官方文檔。 1 場景描述 最近使用 postgresql 命令行比較多,就找了個類似 mycli 工具,pgcli,這個是針對 postgresql 的,興沖沖的安裝了 brew install pgcli 沒想到這貨自動幫我...
摘要:下圖是的代碼段,我喜歡叫它攪拌攪拌再攪拌得出一個隨機數如果看到這里你已經被攪暈了,那讓我再簡單梳理下選擇一個時做的事情給出一個,作為的輸入。,,得出一個隨機數重點是隨機數,不是。對于所有的用他們的權重乘以每個對應的隨機數,得到乘積。前言前文回顧:《開源社區的明星項目—Ceph談》、《史上最全的Ceph構件及組件分析》、關于Ceph主題,這一節將詳細介紹Ceph ?CRUSH。Ceph CRU...
摘要:摘要第九屆中國數據庫技術大會,阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破遷移上云的壁壘為題的演講。于是,阿里云給出了上面的解決方案。 摘要: 2018第九屆中國數據庫技術大會,阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破Oracle遷移上云的壁壘為題的演講。Oracle是指數據庫管理系統,面對Oracle遷移上云的壁壘,阿里云如何能夠打破它呢?本文提出了Oracle 到云數據庫P...
摘要:平臺采用分布式存儲系統作為虛擬化存儲,用于對接虛擬化計算及通用數據存儲服務,消除集中式網關,使客戶端直接與存儲系統進行交互,并以多副本糾刪碼多級故障域數據重均衡故障數據重建等數據保護機制,確保數據安全性和可用性。云計算平臺通過硬件輔助的虛擬化計算技術最大程度上提高資源利用率和業務運維管理的效率,整體降低 IT 基礎設施的總擁有成本,并有效提高業務服務的可用性、可靠性及穩定性。在解決計算資源的...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20