SQL 語法 – Recursive Common Table Expressions

測試環境為 CentOS 8 x86_64 , Mariadb 10.3.17-1 (虛擬機)

MariaDB 版本需要 10.2.2 以上才有支援 Recursive Common Table Expressions .

什麼是 CTE : Common Table Expressions ,它可以把 SELECT 後整個 Table 儲存起來,共後續 SQL 語法來使用.詳細說明請參考 – http://benjr.tw/102498

什麼是 Recursive Common Table Expressions (適用於存取階層式的資料)我們直接來看 MariaDB 提供的範例 – https://mariadb.com/kb/en/recursive-common-table-expressions-overview/ (以下圖示皆是來至於此)來說明.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2774
Server version: 10.3.27-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE folks (id int(11) , name varchar(50),  father varchar(50) , mother varchar(50));
Query OK, 0 rows affected (0.008 sec)

資料為親子的關係(誰是誰的父母親).

INSERT INTO folks VALUES 
  ('100', 'Alex' , 20 , 30), 
  ('20', 'Dad' , 10 , NULL), 
  ('30', 'Mom', NULL , NULL), 
  ('10', 'Grandpa Bill' , NULL , NULL), 
  ('98', 'Sister Amy' , 20 , 30);

MariaDB [testdb]> INSERT INTO folks VALUES 
    ->   ('100', 'Alex' , 20 , 30), 
    ->   ('20', 'Dad' , 10 , NULL), 
    ->   ('30', 'Mom', NULL , NULL), 
    ->   ('10', 'Grandpa Bill' , NULL , NULL), 
    ->   ('98', 'Sister Amy' , 20 , 30);
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM folks;
+------+--------------+--------+--------+
| id   | name         | father | mother |
+------+--------------+--------+--------+
|  100 | Alex         | 20     | 30     |
|   20 | Dad          | 10     | NULL   |
|   30 | Mom          | NULL   | NULL   |
|   10 | Grandpa Bill | NULL   | NULL   |
|   98 | Sister Amy   | 20     | 30     |
+------+--------------+--------+--------+
5 rows in set (0.001 sec)

我們要怎麼查詢 Alex 的親屬關係呢! 一般要透過好幾次的 SELECT 才能達成,透過 Recursive Common Table Expressions 可以快速達成.

WITH RECURSIVE ancestors AS (
SELECT * FROM folks
WHERE name = 'Alex'
UNION
SELECT f.*
FROM folks as f, ancestors AS a
WHERE
  f.id = a.father or f.id = a.mother
)
SELECT * FROM ancestors;

執行結果:

MariaDB [testdb]> WITH RECURSIVE ancestors AS (
    -> SELECT * FROM folks
    -> WHERE name = 'Alex'
    -> UNION
    -> SELECT f.*
    -> FROM folks as f, ancestors AS a
    -> WHERE
    ->   f.id = a.father or f.id = a.mother
    -> )
    -> SELECT * FROM ancestors;
+------+--------------+--------+--------+
| id   | name         | father | mother |
+------+--------------+--------+--------+
|  100 | Alex         | 20     | 30     |
|   20 | Dad          | 10     | NULL   |
|   30 | Mom          | NULL   | NULL   |
|   10 | Grandpa Bill | NULL   | NULL   |
+------+--------------+--------+--------+
4 rows in set (0.001 sec)

為什麼結果是這樣,我們來看一下官方的說明:

  1. 第一段 SELECT 會得到 Alex 本身的資料.
    SELECT * FROM folks
    WHERE name = 'Alex'
    

  2. 第二段 SELECT 會得到 Alex 父母的資料.
    SELECT f.*
    FROM folks as f, ancestors AS a
    WHERE
      f.id = a.father or f.id = a.mother
    

    • FROM folks as f, ancestors AS a
      依據兩個 Table 來做 FROM folks as f(原本的資料) ancestors AS a(剛剛 SELECT Alex 的資料)
    • WHERE f.id = a.father or f.id = a.mother
      比對 f(原本的資料)的 id 與 a(剛剛 SELECT Alex 的資料) 的 father 或是 mother 相同 id 的.

    這時候 ancestors 資料表變成為 Alex 父母的資料.

  3. RECURSIVE 會重覆第二段 SELECT 會拿 Alex 父母的資料去做比對.

    這樣會查到 Alex 祖父母的資料.這時候 ancestors 資料表變成為 Alex 祖父母的資料.
  4. RECURSIVE 會重覆第二段 SELECT 會拿 Alex 祖父母的資料去做比對,沒有資料後就停止 RECURSIVE .
  5. 最後 UNION 這些資料.
  6. 最後透過 SELECT 讀取完整 ancestors 資料表的資料.
    SELECT * FROM ancestors;
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料