我現在是用 10.2.7
雖然 CTE 在其他資料庫上可以用很久了
但是從MySQL入門
甚至一直以來都只用MySQL的人應該對這很陌生
因為MySQL還要等到未來的 MySQL 8 才會支援 with
根據官方對 CTE 的描述
Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. There are two kinds of CTEs: Non-Recursive, which this article covers; and Recursive.
CTEs 是標準的SQL功能,本質上是一個有名字的結果集合,有兩種 CTEs ,非 recursive 跟 recursive 。
白話點說,也就是給了名字的子查詢啦
但比子查詢更好用的地方在於
有了名字就可以重複使用
在過去子查詢放在某個地方之後
就只能在那邊使用
如果想在別的地方用到同樣的子查詢資料
就只能再查詢一次
實在很浪費資源
使用 CTEs 就沒這個困擾了
這裡引用官方的例子:
WITH engineers AS(
SELECT
*
FROM
employees
WHERE
dept IN(
'Development',
'Support'
)
),
SELECT
*
FROM
engineers E1
WHERE
NOT EXISTS(
SELECT
1
FROM
engineers E2
WHERE
E2.country = E1.country
AND E2.name <> E1.name
);
這段是在找 某幾個部門之內
不同人之間卻是住同一個城市的人員資料
這在以前要透過 self-join 來做
現在用 with query
就可以很方便做到了
先用 一道子查詢 查出某幾個部門的人
並命名為 employees
再對這個 employees 篩選出兩者名字不同 卻住一樣城市的人
而且這樣可讀性比較好
很清楚可以知道 先篩選出部門
再對剩下的做其他處理
而 recursive query
是建構其之上
官方的例子就非常好懂
有一張家族關係表
如果我要找 Alex 的所有長輩
在過去要用很多子查詢
或是下很多道sql command
並在程式裡處理
現在有了 recursive query
可以用一道指令就全部抓出來
WITH RECURSIVE ancestores AS(
SELECT
*
FROM
folks
where
name = 'Alex'
UNION SELECT
f.*
FROM
folks AS f,
ancestores AS a
WHERE
f.id = a.father
OR f.id = a.mother
)
SELECT * FROM ancestores;
首先他會執行上半部
SELECT * FROM folks where name = ‘Alex’
把Alex抓出來
這部分資料會被看成 ancestors 的結果
第二步開始,就會不斷的抓出要聯集的資料,直到沒資料為止
所以他會抓出 Alex 的爸爸跟媽媽
再塞進 ancestors 的結果裡
他會一直抓資料放到結果裡
直到沒資料並停住
最後就是抓出 Alex 的所有長輩了
這功能實在太好用啦
簡直相見恨晚
尤其把選單放進資料庫內
要抓出某子選單的所有上層選單
就靠這個來抓了!!!
ref:
https://mariadb.com/kb/en/library/non-recursive-common-table-expressions-overview/
https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/