PostgreSQL=>遞迴查詢

funnyZpC發表於2018-01-21
SQL遞迴

PostgreSQL=>遞迴查詢

轉載請註明源地址：http://www.cnblogs.com/funnyzpc/p/8232073.html

　　距上次部落格更新剛好兩週，這兩週發生了很多，比如：SFTP服務拉取資料，第三方公共平臺介面邏輯遷移新框架，新框架(Spring Cloud)上手，公司月報和審計資料獲取等等。。。，差不多都有無盡的坑，尤其是最後者，實是折騰人啊~；牢騷歸牢騷，但是事情還是要認真做的，。。。，就目前來看，這些對於我最大的好處就是有助於快速理解公司業務邏輯；啊哈~，扯完，從這些日子開始抽週末時間學習資料庫->PosgreSQL(個人慣稱：大象

),遂從本節起說PostgreSQL有關的動西。

　　記得在上一家公司的時候做過一個冷門的附加功能，就是把根據傳入的部門ID(一個List)查詢部門下所有的人員，當時是Oracle資料庫配合著Mybatis來做的，中間填過兩個坑，一個是Mybatis的forach的引數個數超過1K會報錯，導致遞迴不能查詢，另一個坑是Oracle的遞迴造型稍難，這個。。。我翻了幾篇部落格寫了好幾行註釋加以理解，希望後來人能明白前人的良苦用心。由於新買MBP 未裝Oracle環境，oracle的遞迴講解就此略過哈(◡‿◡✿)o~

　　首先給出一個測試表(elevel) 關於職稱級別的表，一位數的ID是最大分類(英語、計算機、會計)，然後子級別的parent_id欄位引用父級ID，有些級別比較籠統這裡不討論哈~：

testDB=> select * from elevel order by  rpad(id::varchar,5,'0');

  id  |         name         | parent_id 

------+----------------------+-----------

    1 | 英語                 |          

   11 | 英語專業四八級       |         1

  111 | 英語專業四級         |        11

  112 | 英語專業八級         |        11

   12 | 大學英語三、四、六級 |         1

  121 | 大學英語三級         |        12

  122 | 大學英語四級         |        12

  123 | 大學英語六級         |        12

    2 | 計算機               |          

   21 | NCR計算機等級        |         2

  211 | NCR計算機一級        |        21

  212 | NCR計算機二級        |        21

  213 | NCR計算機三級        |        21

  214 | NCR計算機四級        |        21

   22 | IT認證類考試         |         2

  221 | CISCO認證            |        22

  222 | ORACLE認證           |        22

    3 | 會計                 |          

   31 | 會計從業證           |         3

   32 | 會計職稱             |         3

  321 | 初級職稱(助理會計師) |        32

  322 | 中級職稱(會計師)     |        32

  323 | 高階職稱(高階職稱)   |        32

 3231 | 正高階會計師         |       323

 3232 | 副高階會計師         |       323

(25 rows)

　　資料造型已經給出了，這裡我放出建表語句及測試資料：

 1 -- create table 
 2 CREATE TABLE elevel
 3 (
 4    id          integer,
 5    "name"      CHARACTER VARYING (20),
 6    parent_id   integer
 7 );
 8 
 9 -- insert data
10      INSERT INTO elevel (id, "name", parent_id) VALUES (1, '英語', NULL);
11      INSERT INTO elevel (id, "name", parent_id) VALUES (2, '計算機', NULL);
12      INSERT INTO elevel (id, "name", parent_id) VALUES (3, '會計', NULL);
13      INSERT INTO elevel (id, "name", parent_id) VALUES (11, '英語專業四八級', 1);
14      INSERT INTO elevel (id, "name", parent_id) VALUES (111, '英語專業四級', 11);
15      INSERT INTO elevel (id, "name", parent_id) VALUES (112, '英語專業八級', 11);
16      INSERT INTO elevel (id, "name", parent_id) VALUES (121, '大學英語三級', 12);
17      INSERT INTO elevel (id, "name", parent_id) VALUES (122, '大學英語四級', 12);
18      INSERT INTO elevel (id, "name", parent_id) VALUES (12, '大學英語三、四、六級', 1);
19      INSERT INTO elevel (id, "name", parent_id) VALUES (123, '大學英語六級', 12);
20      INSERT INTO elevel (id, "name", parent_id) VALUES (21, 'NCR計算機等級', 2);
21      INSERT INTO elevel (id, "name", parent_id) VALUES (22, 'IT認證類考試', 2);
22      INSERT INTO elevel (id, "name", parent_id) VALUES (211, 'NCR計算機一級', 21);
23      INSERT INTO elevel (id, "name", parent_id) VALUES (212, 'NCR計算機二級', 21);
24      INSERT INTO elevel (id, "name", parent_id) VALUES (213, 'NCR計算機三級', 21);
25      INSERT INTO elevel (id, "name", parent_id) VALUES (214, 'NCR計算機四級', 21);
26      INSERT INTO elevel (id, "name", parent_id) VALUES (221, 'CISCO認證', 22);
27      INSERT INTO elevel (id, "name", parent_id) VALUES (222, 'ORACLE認證', 22);
28      INSERT INTO elevel (id, "name", parent_id) VALUES (31, '會計從業證', 3);
29      INSERT INTO elevel (id, "name", parent_id) VALUES (32, '會計職稱', 3);
30      INSERT INTO elevel (id, "name", parent_id) VALUES (321, '初級職稱(助理會計師)', 32);
31      INSERT INTO elevel (id, "name", parent_id) VALUES (322, '中級職稱(會計師)', 32);
32      INSERT INTO elevel (id, "name", parent_id) VALUES (323, '高階職稱(高階職稱)', 32);
33      INSERT INTO elevel (id, "name", parent_id) VALUES (3231, '正高階會計師', 323);
34      INSERT INTO elevel (id, "name", parent_id) VALUES (3232, '副高階會計師', 323);
35      COMMIT;

　　現在我定一個需求：查詢“會計”(id=3)類別下的所有的子記錄（包含id=3的記錄）：

1 WITH RECURSIVE le (id,name,parent_id) as 
2 (
3  select id,name,parent_id from elevel where id=3
4  union all
5  select e2.id,e2.name,e2.parent_id from elevel e2,le e3 where e3.id=e2.parent_id 
6 )
7  select * from le order by rpad(id::varchar,5,'0') asc;

查詢結果：　

  id  |         name         | parent_id 

------+----------------------+-----------

    3 | 會計                 |          

   31 | 會計從業證           |         3

   32 | 會計職稱             |         3

  321 | 初級職稱(助理會計師) |        32

  322 | 中級職稱(會計師)     |        32

  323 | 高階職稱(高階職稱)   |        32

 3231 | 正高階會計師         |       323

 3232 | 副高階會計師         |       323

(8 rows)

根據以上查詢結果，這裡敲黑板，劃重點：

　　=>“RECURSIVE” 是PostgreSQL的關鍵字不是具體存在的表

　　=>第一行中的："(id,name,parent_id)"定義的是虛擬el表的引數，欄位的名稱可隨意，但欄位的個數一定要與3~5行中的查詢結果的個數一致！

　　=>"el"是宣告的虛擬表，每次遞迴一層後都會將本層資料寫入el中

　　=>第三行中的id=3是需要查詢開始層的ID，關鍵是第五行=>需要將虛擬表“el"表與“elevel”實體表連表查詢

　　=>特別需要注意的是第三行中的中的where條件(e3.id=e2.parent_id) ,取虛擬表的ID和實體表parent_id連

　　　　這個條件決定了當前遞迴查詢的查詢方式(向上查詢還是向下查詢)；

　　=>第三行的遞迴開始查詢不可缺少，不然查詢報錯，個人理解這是PostgreSQL根據首行的記錄來遞迴子記錄

好了，需要總結的大概就是這些，至於第七行中的rpad函式是向右補齊的函式，用於排序的需要，讀者可以略去order by之後的內容。

　　好了，一個簡單的遞迴查詢就成了，嗯。。。，如需求同學說：我需要將每條記錄的遞迴結構(path)和層級(depath)的順序都顯示出來。

　　遺憾的是PG遞迴查詢本身並沒有提供相應的函式和關鍵字來方便我們的需求，怎麼辦=>加欄位：

1 with RECURSIVE le (id,name,parent_id,path,depath) as 
2 (
3  select id,name,parent_id,Array[id] as path,1 as depath from elevel where id=3
4  union all
5  select e2.id,e2.name,e2.parent_id,e3.path||e3.id,e3.depath+1 
6      from elevel e2,le e3 where e3.id=e2.parent_id 
7 )
8 select * from le order by rpad(id::varchar,5,'0') asc;

查詢結果：


  id  |         name         | parent_id |     path     | depath 

------+----------------------+-----------+--------------+--------

    3 | 會計                 |           | {3}          |      1

   31 | 會計從業證           |         3 | {3,3}        |      2

   32 | 會計職稱             |         3 | {3,3}        |      2

  321 | 初級職稱(助理會計師) |        32 | {3,3,32}     |      3

  322 | 中級職稱(會計師)     |        32 | {3,3,32}     |      3

  323 | 高階職稱(高階職稱)   |        32 | {3,3,32}     |      3

 3231 | 正高階會計師         |       323 | {3,3,32,323} |      4

 3232 | 副高階會計師         |       323 | {3,3,32,323} |      4

(8 rows)

　　嗯~，可以看到查詢SQL與之上的查詢不同的是第三行中定義了一個"Array[id]" 的遞迴結構欄位，最為和一個“1” 的深度欄位，Array函式是PostgreSQL特有的陣列函式，讀者可以自行查閱資料瞭解哈( ^)o(^ )~。

　　當然以上查詢語句滿足既已有的需求，想下->如果這裡變我最成最初我做過的那個需求(查詢部門下的所有人，不含部門記錄)，該怎麼辦呢。

　　額~，遞迴本身提供給我們的結果已經趨於完美了，由於官方api並沒有提供進一步的方法，這裡只有從查詢結果著手解決這個問題囖~

with RECURSIVE le (id,name,parent_id,path,depath) as 
(
 select id,name,parent_id,Array[id] as path,1 as depath from elevel where id=3
 union all
 select e2.id,e2.name,e2.parent_id,e3.path||e3.id,e3.depath+1 
     from elevel e2,le e3 where e3.id=e2.parent_id 
)
select * from le l where 0=(select count(1) from le where parent_id=l.id) order by rpad(id::varchar,5,'0') asc;

查詢結果：

  id  |         name         | parent_id |     path     | depath 

------+----------------------+-----------+--------------+--------

   31 | 會計從業證           |         3 | {3,3}        |      2

  321 | 初級職稱(助理會計師) |        32 | {3,3,32}     |      3

  322 | 中級職稱(會計師)     |        32 | {3,3,32}     |      3

 3231 | 正高階會計師         |       323 | {3,3,32,323} |      4

 3232 | 副高階會計師         |       323 | {3,3,32,323} |      4

(5 rows)

　根據以上查詢SQL來看，答案其實很簡單，在遞迴完成後將存在子記錄的用where條件過濾掉即可(見查詢語句最後一行)

嗯，以上幾個例子全部是向下遞迴查詢，下面我展示下向上查詢的語句，很簡單=>

 

1  with RECURSIVE le (id,name,parent_id) as 
2  (
3   select id,name,parent_id from elevel where id=323
4   union all
5   select e2.id,e2.name,e2.parent_id from elevel e2,le e3 where e3.parent_id=e2.id
6  )
7  select * from le order by rpad(id::varchar,5,'0') asc;

查詢結果：

 id  |        name        | parent_id 

-----+--------------------+-----------

   3 | 會計               |          

  32 | 會計職稱            |   　    3

 323 | 高階職稱(高階職稱)   |        32

可以看到與向上查詢的查詢語句相差不幾，關鍵，關鍵是=>第5行的where條件，很意外吧，如此小的改動就有查詢方向上的變化，個人對此的理解是：

　　=>遞迴向下查詢是用虛擬表的id去聯結遞迴表的parent_id

　　=>遞迴向上查詢是用虛擬表的parent_id去聯結遞迴表的id

　　本人愚鈍，目前對於兩者的區別發現僅限於此，歡迎讀者點撥哈。。。~

最後，需要說明的是，在公司業務滿足的情況下儘可能用單層查詢語句查詢，尤其對於層級較少較固定的結構下較為合適，此建議主要針對的是遞迴的兩大問題而言：

　　1>遞迴的查詢效率較低，尤其是記錄較多層級龐大的記錄

　　2>若現有記錄的層級如有交叉，極容易導致遞迴死迴圈，這點尤其要注意

　　

OK, 本節完成，下節開始講：“視窗函式”

現在是：2018-01-21 21:20:50，願各位晚安，明天要上班哦~

 

  

相關文章