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 語法來使用.詳細說明請參考 – https://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;

程式主要可以區分為兩區

• Anchor Part
  只會執行一次,資料為外部資料表.

  SELECT * FROM folks
  WHERE name = 'Alex'
  

• Recursive Part
  會重複執行直到 Where 判斷為非,資料為 CTE 本身資料表.

  SELECT f.*
  FROM folks as f, ancestors AS a
  WHERE
    f.id = a.father or f.id = a.mother
  

執行結果:

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;