SQL 高級查詢

前面我們寫了一下 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