PostgreSQL樹形結構的遞歸查詢示例_PostgreSQL

來源:腳本之家  責任編輯:小易  

psql里查看所有庫:\\l查看所有表:\\dt查看某個表的結構:\\d 表名如果表位于你的自定義模式中,要把模式名加入“搜索路徑”。具體來說就是編輯postgres的配置文件:<數據目錄>\\postgres.conf,search_path=這行www.13333515.buzz防采集請勿采集本網。

背景

處理不確定深度的層級結構,比如組織機構,一個常用的設計是在一張表里面保存 ID 和 Parent_ID ,并且通過自聯結的辦法構造一顆樹。這種方式對寫數據的過程很友好,但是查詢過程就變得相對復雜。在不引入MPTT模型的前提下,必須通過遞歸算法來查詢某個節點和下級子節點。

1、通過命令行查詢 \\d 數據庫—得到所有表抄的名字 \\d 表名—得到表結構 2、通過SQL語句查詢襲 select*from pg_tables\"—得到當前百db中所有表的信息(這里pg_tables是系統視圖) select

Oracle提供的connect by擴展語法,簡單好用。但是其他的RDBMS就沒這么人性化了(或者我不知道)。最近在項目中使用PostgreSQL來查詢樹形數據,記錄一下。

Oracle 的例子 SQL>SELECT 2* 3 FROM 4 test_main;ID VALUE 1 ONE 2 TWO SQL>CREATE TABLE test_t AS SELECT*FROM test_main;表已創建。SQL>select*FROM test_t;ID VALUE 1 ONE 2 TWO postgresql

構造樣本數據

1)頁面管理子模塊:對PostgreSQL緩沖區頁面的組織結構進行定義以及提供頁面操作的方法。2)緩沖區管理子模塊:管理PostgreSQL的緩沖區,包括本地緩沖區和共享緩沖區。3)存儲設備管理子模塊:數據庫記錄

以 test 作為用戶名,導出 localhost:5432 服務器上的 Test 數據庫。導出的文件名為 test_data F:\\PostgreSQL\\9.2\\bin>pg_dump-f test_data-h localhost-U test-p 5432 Test 口令: F:\\PostgreSQL

drop table if exists demo.tree_data;create table demo.tree_data ( id integer, code text, pid integer, sort integer);insert into demo.tree_data values(1, '中國', null, 1);insert into demo.tree_data values(2, '四川', 1, 1);insert into demo.tree_data values(3, '云南', 1, 2);insert into demo.tree_data values(4, '成都', 2, 1);insert into demo.tree_data values(5, '綿陽', 2, 2);insert into demo.tree_data values(6, '武侯區', 4, 1);insert into demo.tree_data values(7, '昆明', 3, 1);

pg_dump-U database_username database-t tablename-f/tmp/tablename.sql恢復zhidao數據表,包含表結構。登入數據庫后 \\i/tmp/tablename.sql即可創版建表。注意此方式恢復在恢復已有表時可能會

connectby函數

如果安裝了 tablefunc 擴展,就可以使用PG版本的connectby函數。這個沒有Oracle那么強大,但是可以滿足基本要求。

以 test 作為用戶名,導出 localhost:5432 服務器上的 Test 數據庫。導出的文件名為 test_data F:\\PostgreSQL\\9.2\\bin>pg_dump-f test_data-h localhost-U test-p 5432 Test 口令: F:\\PostgreSQL

-- API 如下connectby(text relname, -- 表名稱 text keyid_fld, -- id字段 text parent_keyid_fld-- 父id字段 [, text orderby_fld ], -- 排序字段 text start_with, -- 起始行的id值 int max_depth-- 樹深度,0表示無限 [, text branch_delim ])-- 路徑分隔符

-- 基本用法如下,必須通過AS子句定義返回的字段名稱和類型select * from connectby('demo.tree_data', 'id', 'pid', 'sort', '1', 0, '~')as (id int, pid int, lvl int, branch text, sort int);-- 查詢結果id | pid | lvl | branch | sort----+-----+-----+---------+------ 1 | | 0 | 1 | 1 2 | 1 | 1 | 1~2 | 2 4 | 2 | 2 | 1~2~4 | 3 6 | 4 | 3 | 1~2~4~6 | 4 5 | 2 | 2 | 1~2~5 | 5 3 | 1 | 1 | 1~3 | 6 7 | 3 | 2 | 1~3~7 | 7(7 rows)

-- 僅僅使用基本用法,只能查詢出id的相關信息,如果要查詢code等其他字段,就需要通過額外的join操作來實現。select t.id, n.code, t.pid, p.code as pcode, lvl, branchfrom (select * from connectby('demo.tree_data', 'id', 'pid', 'sort', '1', 0, '~')as (id int, pid int, lvl int, branch text, sort int)) as tleft join demo.tree_data as n on (t.id = n.id)left join demo.tree_data as p on (t.pid = p.id)order by t.sort ; id | code | pid | pcode | lvl | branch----+--------+-----+-------+-----+--------- 1 | 中國 | | | 0 | 1 2 | 四川 | 1 | 中國 | 1 | 1~2 4 | 成都 | 2 | 四川 | 2 | 1~2~4 6 | 武侯區 | 4 | 成都 | 3 | 1~2~4~6 5 | 綿陽 | 2 | 四川 | 2 | 1~2~5 3 | 云南 | 1 | 中國 | 1 | 1~3 7 | 昆明 | 3 | 云南 | 2 | 1~3~7(7 rows)

PS:雖然通過join可以查詢出節點的code,但是branch部分不能直接轉換成對應的code,使用上還是不太方便。

CTE語法

使用CTE語法,通過 with recursive 來實現樹形數據的遞歸查詢。這個方法雖然沒有connectby那么直接,但是靈活性和顯示效果更好。

以 test 作為用戶名,導出 localhost:5432 服務器上的 Test 數據庫。導出的文件名為 test_data F:\\PostgreSQL\\9.2\\bin>pg_dump-f test_data-h localhost-U test-p 5432 Test 口令: F:\\PostgreSQL

-- with recursive cte as( -- 先查詢root節點 select id, code, pid, '' as pcode, code as branch from demo.tree_data where id = 1 union all -- 通過cte遞歸查詢root節點的直接子節點 select origin.id, origin.code, cte.id as pid, cte.code as pcode, cte.branch || '~' || origin.code from cte join demo.tree_data as origin on origin.pid = cte.id)select id,code, pid, pcode, branch, -- 通過計算分隔符的個數,模擬計算出樹形的深度 (length(branch)-length(replace(branch, '~', ''))) as lvlfrom cte;-- id | code | pid | pcode | branch | lvl----+--------+-----+-------+-----------------------+----- 1 | 中國 | | | 中國 | 0 2 | 四川 | 1 | 中國 | 中國~四川 | 1 3 | 云南 | 1 | 中國 | 中國~云南 | 1 4 | 成都 | 2 | 四川 | 中國~四川~成都 | 2 5 | 綿陽 | 2 | 四川 | 中國~四川~綿陽 | 2 7 | 昆明 | 3 | 云南 | 中國~云南~昆明 | 2 6 | 武侯區 | 4 | 成都 | 中國~四川~成都~武侯區 | 3(7 rows)

執行過程說明

從上面的例子可以看出,WITH RECURSIVE語句包含了兩個部分 non-recursive term(非遞歸部分),即上例中的union all前面部分 recursive term(遞歸部分),即上例中union all后面部分

執行步驟如下 執行non-recursive term。(如果使用的是union而非union all,則需對結果去重)其結果作為recursive term中對result的引用,同時將這部分結果放入臨時的working table中 重復執行如下步驟,直到working table為空:用working table的內容替換遞歸的自引用,執行recursive term,(如果使用union而非union all,去除重復數據),并用該結果(如果使用union而非union all,則是去重后的結果)替換working table

以上面的query為例,來看看具體過程

執行non-recursive query

-- step 1 執行 select id, code, pid, '' as pcode, code as branch from demo.tree_data where id = 1 -- 結果集和working table為 id | code | pid | pcode | branch----+------+-----+-------+-------- 1 | 中國 | | | 中國

執行recursive query

-- step 2 執行遞歸,此時自引用cte中的數據是step 1的結果 select origin.id, origin.code, cte.id as pid, cte.code as pcode, cte.branch || '~' || origin.code from cte join demo.tree_data as origin on origin.pid = cte.id -- 結果集和working table為 id | code | pid | pcode | branch ----+--------+-----+-------+--------------------- 2 | 四川 | 1 | 中國 | 中國~四川 3 | 云南 | 1 | 中國 | 中國~云南

3、繼續執行recursive query,直到結果集和working table為空

4、結束遞歸,將前三個步驟的結果集合并,即得到最終的WITH RECURSIVE的結果集。

嚴格來講,這個過程實現上是一個迭代的過程而非遞歸,不過RECURSIVE這個關鍵詞是SQL標準委員會定立的,所以PostgreSQL也延用了RECURSIVE這一關鍵詞。

總結

以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對真格學網的支持。

在數據庫運維工作中,經常會有數據目錄使用率較高需要調整的情況,通常會給數據庫建立多個表空間,并分別位于不同的盤上,這時需要做的工作就是調整庫中現有表和索引的表空間,下面簡單總結下這塊維護 工作的內容,以下都是基于 PostgreSQL 9.0內容來自www.13333515.buzz請勿采集。


  • 本文相關:
  • postgresql 如何獲取當前日期時間及注意事項
  • centos7 安裝 postgresql11的方法步驟
  • postgresql數據庫中窗口函數的語法與使用
  • postgresql實現批量插入、更新與合并操作的方法
  • postgresql存儲過程用法實戰詳解
  • mac系統重置postgresql密碼的方法示例代碼
  • 用一整天的時間安裝postgresql ntfs權限
  • pgsql查詢優化之模糊查詢實例詳解
  • postgresql alter語句常用操作小結
  • postgresql中的xml操作函數代碼
  • windows postgresql 9.1 安裝詳細步驟
  • postgresql教程(四):數據類型詳解
  • postgresql圖(graph)的遞歸查詢實例
  • postgresql分區表(partitioning)應用實例詳解
  • windows下postgresql數據庫的下載與配置方法
  • phppgadmin 配置文件參數說明中文版
  • postgreSQL有沒有實現樹形結構查詢
  • 如何查看postgresql中的表結構
  • 如何查看postgresql中的表結構
  • 如何查看postgresql中的表結構
  • 如何查PostgreSQL 數據庫中所有的表
  • postgresql數據庫中怎么復制表結構和完全復制表,如oracle中create table a as select * from b
  • postgresql 查看有哪些表
  • 如何在PostgreSQL中備份一張表(包含表結構)和恢復
  • 如何在PostgreSQL中備份和恢復一張表(包含表結構)
  • MySQL 和 PostgreSQL 哪個更適合做金融數據庫
  • 網站首頁網頁制作腳本下載服務器操作系統網站運營平面設計媒體動畫電腦基礎硬件教程網絡安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess數據庫文摘數據庫其它首頁postgresqlpostgresql 如何獲取當前日期時間及注意事項centos7 安裝 postgresql11的方法步驟postgresql數據庫中窗口函數的語法與使用postgresql實現批量插入、更新與合并操作的方法postgresql存儲過程用法實戰詳解mac系統重置postgresql密碼的方法示例代碼用一整天的時間安裝postgresql ntfs權限pgsql查詢優化之模糊查詢實例詳解postgresql alter語句常用操作小結postgresql中的xml操作函數代碼windows postgresql 9.1 安裝詳細步驟postgresql教程(四):數據類型詳解postgresql圖(graph)的遞歸查詢實例postgresql分區表(partitioning)應用實例詳解windows下postgresql數據庫的下載與配置方法phppgadmin 配置文件參數說明中文版postgresql 角色與用戶管理介紹windows下postgresql數據庫的下載windows下postgresql安裝圖解15個postgresql數據庫實用命令分postgresql中的oid和xid 說明windows postgresql 安裝圖文教程postgresql alter語句常用操作小postgresql 安裝和簡單使用postgresql 創建表分區postgresql新手入門教程15個postgresql數據庫實用命令分享postgresql教程(三):表的繼承和分區表詳postgresql教程(十):性能提升技巧postgresql error: invalid escape strinpostgresql教程(十五):系統表詳解postgresql教程(五):函數和操作符詳解(postgresql數據庫中窗口函數的語法與使用postgresql實現一個通用標簽系統postgresql教程(十四):數據庫維護windows下postgresql安裝圖解
    免責聲明 - 關于我們 - 聯系我們 - 廣告聯系 - 友情鏈接 - 幫助中心 - 頻道導航
    Copyright © 2017 www.13333515.buzz All Rights Reserved
    3排列五开奖结果