前面我們寫了一下 SQL 的極簡入門，今天來說點高級查詢。沒看到的朋友可以點擊下面鍊接查看。

1 小時 SQL 極速入門（一）

1 小時 SQL 極速入門（二）

1 小時 SQL 極速入門（三）——分析函數

層次化查詢

層次化結構可以理解為樹狀數據結構，由節點構成。比如常見的組織結構由一個總經理，多個副總經理，多個部門部長組成。再比如在生産制造中一件産品會有多個子零件組成。舉個簡單的例子，如下圖所示

汽車作為根節點，下面包含發動機和車身兩個子節點，而子節點又是由其他葉節點構成。(葉節點表示沒有子節點的節點)

假如我們要把這些産品信息存儲到數據庫中，會形成如下數據表。

我們用 parent_product_id 列表示當前産品的父産品是哪一個。

那麼用 SQL 語句如何進行層次化查詢呢？這裡就要用到 CONNECT BY 和 START WITH 語法。

我們先把 SQL 寫出來，再來解釋其中的含義。

SELECT LEVEL, id, parent_product_id, name FROM product START WITH id = 1 CONNECT BY prior id = parent_product_id ORDER BY level

查詢結果如下：

解釋一下：LEVEL 列表示當前産品屬于第幾層級。START WITH 表示從哪一個産品開始查詢,CONNECT BY PRIOR 表示父節點與子節點的關系，每一個産品的 ID 指向一個父産品。

如果我們把 START WITH 的查詢起點改為 id = 2,重新運行上面的 SQL 語句将會得到如下結果：

因為 id=2 的産品是車身，我們就隻能查到車身下面的子産品。

當然，我們可以把查詢結果美化一下，使其更有層次感，我們讓根節點下面的 LEVEL 前面加幾個空格即可。把上面的 SQL 稍微修改一下。為每個 LEVEL 前面增加 2*(LEVEL-1)個空格，這樣第二層就會增加兩個空格，第三層會增加四個空格。

SELECT level, id, parent_product_id, LPAD(' ', 2 * (level - 1)) || name AS name FROM product START WITH id = 1 CONNECT BY prior id = parent_product_id

查詢結果已經有了層次感，如下圖：

遞歸查詢

除了使用上面我們說的方法，還可以使用遞歸查詢得到同樣的結果。遞歸會用到 WITH 語句。普通的 WITH 語句可以看作一個子查詢，我們在 WITH 外部可以直接使用這個子查詢的内容。

當遞歸查詢時，我們是在 WITH 語句内部來引用這個子查詢。還是上面的例子，我們使用 WITH 語句來查詢。

WITH temp_product (product_level, id, parent_product_id,name) AS ( SELECT 0 AS product_level,id,parent_product_id,name FROM product WHERE parent_product_id IS NULL UNION ALL SELECT tp.product_level 1,p.id, p.parent_product_id, p.name FROM product p JOIN temp_product tp ON p.parent_product_id=tp.id ) SELECT product_level, id, parent_product_id, LPAD(' ', 2 * product_level) || name AS NAME FROM temp_product;

第一條 SELECT 語句我們查詢出來了根節點，并且設置為 level = 0,第二條SELECT 語句關聯上 WITH 語句自身，并且 level 每層加 1 進行遞歸。

查詢結果如下：

可以看到第一列是展示的産品層級，和我們上面查詢出來的結果是一緻的。

同時使用 WITH 遞歸時還可以使用深度優先搜索和廣度優先搜索，什麼意思呢？廣度優先就是在返回子行之前首先返回兄弟行，如上圖，首先把車身和發動機兩個兄弟行返回，之後是他們下面的子行。相反，深度優先就是首先返回一個父節點的子行再返回另一個兄弟行。

我們隻需要在 SELECT 語句上方加上下面語句即可實現深度優先搜索查詢。

search depth FIRST BY id SET order_by_id

結果如下，看到首先返回每個父節點下的子行，再返回另一個父節點。

同理，廣度優先使用的是下面的 SQL 語句

search breadth FIRST BY id SET order_by_id

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!