SQL 高級查詢

前面我們寫了一下 SQL 的極簡入門，今天來說點高級查詢。沒看到的朋友可以點擊下面鏈接查看。
1 小時 SQL 極速入門（一）
1 小時 SQL 極速入門（二）
1 小時 SQL 極速入門（三）

層次化查詢

層次化結(jié)構(gòu)可以理解為樹狀數(shù)據(jù)結(jié)構(gòu)，由節(jié)點構(gòu)成。比如常見的組織結(jié)構(gòu)由一個總經(jīng)理，多個副總經(jīng)理，多個部門部長組成。再比如在生產(chǎn)制造中一件產(chǎn)品會有多個子零件組成。舉個簡單的例子，如下圖所示

層次圖.png

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

假如我們要把這些產(chǎn)品信息存儲到數(shù)據(jù)庫中，會形成如下數(shù)據(jù)表。

表結(jié)構(gòu).png

我們用 parent_product_id 列表示當(dāng)前產(chǎn)品的父產(chǎn)品是哪一個。

那么用 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

查詢結(jié)果如下：

查詢1.png

解釋一下：LEVEL 列表示當(dāng)前產(chǎn)品屬于第幾層級。START WITH 表示從哪一個產(chǎn)品開始查詢,CONNECT BY PRIOR 表示父節(jié)點與子節(jié)點的關(guān)系，每一個產(chǎn)品的 ID 指向一個父產(chǎn)品。

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

查詢2.png

因為 id=2 的產(chǎn)品是車身，我們就只能查到車身下面的子產(chǎn)品。

當(dāng)然，我們可以把查詢結(jié)果美化一下，使其更有層次感，我們讓根節(jié)點下面的 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

查詢結(jié)果已經(jīng)有了層次感，如下圖：

圖三.png

遞歸查詢

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

當(dāng)遞歸查詢時，我們是在 WITH 語句內(nèi)部來引用這個子查詢。還是上面的例子，我們使用 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 語句我們查詢出來了根節(jié)點，并且設(shè)置為 level = 0,第二條SELECT 語句關(guān)聯(lián)上 WITH 語句自身，并且 level 每層加 1 進行遞歸。

查詢結(jié)果如下：

WITH.png

可以看到第一列是展示的產(chǎn)品層級，和我們上面查詢出來的結(jié)果是一致的。

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

我們只需要在 SELECT 語句上方加上下面語句即可實現(xiàn)深度優(yōu)先搜索查詢。

  search depth FIRST BY id
  SET order_by_id

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

深度優(yōu)先.png

同理，廣度優(yōu)先使用的是下面的 SQL 語句

  search breadth FIRST BY id
  SET order_by_id