小巧、快速、可靠。
任選三項。
任選三項。
常見表格運算式或 CTE 就像臨時檢視，只存在於單一 SQL 陳述式的期間。有兩種常見表格運算式：「一般」和「遞迴」。一般常見表格運算式有助於讓查詢更容易理解，方法是將子查詢分解出主要 SQL 陳述式。遞迴常見表格運算式提供執行樹狀或遞迴查詢樹狀結構和圖形的能力，這是 SQL 語言中原本沒有的功能。
所有常見表格運算式（一般和遞迴）都是透過在SELECT、INSERT、DELETE或UPDATE陳述式之前加上 WITH 子句來建立。單一 WITH 子句可以指定一個或多個常見表格運算式，其中一些是一般的，而另一些則是遞迴的。
一般常見表格運算式就像檢視，存在於單一陳述式的期間。一般常見表格運算式對於分解子查詢和讓整體 SQL 陳述式更容易閱讀和理解很有用。
即使 WITH 子句包含 RECURSIVE 關鍵字，它也可以包含一般常見表格運算式。使用 RECURSIVE 不會強制常見表格運算式為遞迴。
遞迴常見表格運算式可用於撰寫查詢，以遍歷樹狀結構或圖形。遞迴常見表格運算式具有與一般常見表格運算式相同的基本語法，但具有下列額外屬性
換句話說，遞迴共用表格表達式必須類似下列內容
在上述圖表中，initial-select表示一個或多個非遞迴 SELECT 陳述式，而recursive-select表示一個或多個遞迴 SELECT 陳述式。最常見的情況是，只有一個initial-select和只有一個recursive-select，但允許多個。
在遞迴共用表格表達式中，將由 cte-table-name 命名的表格稱為「遞迴表格」。在上述 recursive-cte 泡沫圖中，遞迴表格必須在 recursive-select 中每個頂層 SELECT 陳述式的 FROM 子句中只出現一次，且不能出現在 initial-select 或 recursive-select 中的任何其他地方，包括子查詢。 initial-select 可以是 複合選取，但不能包含 ORDER BY、LIMIT 或 OFFSET。 recursive-select 也可以是 複合選取，但限制是該複合的所有元素必須由將 initial-select 與 recursive-select 分開的 UNION 或 UNION ALL 算子分隔。 recursive-select 可以包含 ORDER BY、LIMIT 和/或 OFFSET，但不能使用 聚合函數 或 視窗函數。
recursive-select 可以是複合選取的功能已在 版本 3.34.0（2020-12-01）中新增。在早期版本的 SQLite 中， recursive-select 只能是單一的簡單 SELECT 陳述式。
計算遞迴表格內容的基本演算法如下
上述基本程序可能會因下列附加規則而有所修改
如果 UNION 運算子將 initial-select 與 recursive-select 連接，則僅在先前未將相同列新增至佇列時，才將列新增至佇列。重複的列會在新增至佇列之前捨棄，即使重複的列已由遞迴步驟從佇列中擷取。如果運算子為 UNION ALL，則由 initial-select 和 recursive-select 產生的所有列，即使重複，也會始終新增至佇列。在判斷列是否重複時，NULL 值彼此相等，且不與任何其他值相等。
如果存在 LIMIT 子句，則會決定在步驟 2b 中將新增至遞迴表格中的最大列數。達到限制後，遞迴將停止。限制為零表示從未將列新增至遞迴表格，而負限制表示可以將無限數量的列新增至遞迴表格。
如果存在 OFFSET 子句，且具有正值 N，則會防止將前 N 列新增至遞迴表格。前 N 列仍由 recursive-select 處理，只是不會新增至遞迴表格。在略過所有 OFFSET 列之前，不會計算列以滿足 LIMIT。
如果存在 ORDER BY 子句，則會決定在步驟 2a 中從佇列中擷取列的順序。如果沒有 ORDER BY 子句，則擷取列的順序未定義。（在目前的實作中，如果省略 ORDER BY 子句，則佇列會變成 FIFO，但應用程式不應依賴此事實，因為它可能會改變。）
下列查詢傳回 1 到 1000000 之間的所有整數
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000) SELECT x FROM cnt;
思考這個查詢如何運作。initial-select 會先執行，並傳回單一列，其中包含單一欄位「1」。這列會加入佇列。在步驟 2a 中，那一列會從佇列中取出，並加入「cnt」。接著，遞迴選取會根據步驟 2c 執行，產生單一新列，其值為「2」，並加入佇列。佇列仍有一列，因此步驟 2 會重複。步驟 2a 和 2b 會取出「2」列，並加入遞迴表格。接著，包含 2 的列會用作遞迴表格的完整內容，而遞迴選取會再次執行，導致值為「3」的列會加入佇列。這會重複 999999 次，直到最後在步驟 2a 中，佇列上唯一的數值為包含 1000000 的列。該列會取出，並加入遞迴表格。但這次，WHERE 子句會導致遞迴選取不傳回任何列，因此佇列會保持為空，而遞迴會停止。
最佳化註解：在上述討論中，類似「將列插入遞迴表格」的陳述應從概念上理解，而非逐字理解。這聽起來像是 SQLite 正在累積一個包含一百萬列的龐大表格，然後回頭從上到下掃描該表格，以產生結果。實際上發生的是，查詢最佳化器會發現「cnt」遞迴表格中的數值只會使用一次。因此，當每一列加入遞迴表格時，該列會立即作為主要 SELECT 陳述的結果傳回，然後捨棄。SQLite 不會 累積一個包含一百萬列的暫時表格。執行上述範例只需要極少的記憶體。然而，如果範例使用 UNION 取代 UNION ALL，則 SQLite 必須保留所有先前產生的內容，以檢查是否有重複項。因此，可行的情況下，程式設計師應盡量使用 UNION ALL 取代 UNION。
以下是前一個範例的變異
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 1000000
)
SELECT x FROM cnt;
這個變異中有兩個差異。initial-select 是「SELECT 1」而非「VALUES(1)」。但這些只是表達完全相同事情的不同語法。另一個變更在於遞迴是由 LIMIT 而不是 WHERE 子句停止的。使用 LIMIT 表示當一百萬列新增到「cnt」表格（並由主 SELECT 傳回，感謝查詢最佳化器）時，遞迴會立即停止，而不管佇列中可能剩下多少列。在更複雜的查詢中，有時很難確保 WHERE 子句最終會導致佇列清空且遞迴終止。但 LIMIT 子句會永遠停止遞迴。因此，如果已知遞迴大小的上限，則永遠包含 LIMIT 子句作為安全措施是很好的做法。
考慮一個表格，其中描述組織成員以及組織內的指揮鏈
CREATE TABLE org( name TEXT PRIMARY KEY, boss TEXT REFERENCES org, height INT, -- other content omitted );
組織中的每位成員都有姓名，而且大多數成員都有單一主管。（整個組織的負責人具有 NULL 的「主管」欄位。）「org」表格的列形成一棵樹。
以下是計算愛麗絲組織中所有人（包括愛麗絲）平均身高的查詢
WITH RECURSIVE
works_for_alice(n) AS (
VALUES('Alice')
UNION
SELECT name FROM org, works_for_alice
WHERE org.boss=works_for_alice.n
)
SELECT avg(height) FROM org
WHERE org.name IN works_for_alice;
下一個範例在單一 WITH 子句中使用兩個常見表格運算式。下列表格記錄家譜
CREATE TABLE family( name TEXT PRIMARY KEY, mom TEXT REFERENCES family, dad TEXT REFERENCES family, born DATETIME, died DATETIME -- NULL if still alive -- other content );
「family」表格類似於早期的「org」表格，但現在每位成員有兩個父母。我們想要知道愛麗絲所有在世的祖先，從最年長到最年輕。首先定義一個常見表格運算式「parent_of」。該常見 CTE 是可用于尋找任何個人的所有父母的檢視。然後在「ancestor_of_alice」遞迴 CTE 中使用該常見 CTE。然後在最後的查詢中使用遞迴 CTE
WITH RECURSIVE
parent_of(name, parent) AS
(SELECT name, mom FROM family UNION SELECT name, dad FROM family),
ancestor_of_alice(name) AS
(SELECT parent FROM parent_of WHERE name='Alice'
UNION ALL
SELECT parent FROM parent_of JOIN ancestor_of_alice USING(name))
SELECT family.name FROM ancestor_of_alice, family
WHERE ancestor_of_alice.name=family.name
AND died IS NULL
ORDER BY born;
假設您有一個無向圖形，其中每個節點由整數識別，而邊界由類似下方的表格定義
CREATE TABLE edge(aa INT, bb INT); CREATE INDEX edge_aa ON edge(aa); CREATE INDEX edge_bb ON edge(bb);
索引並非必要，但它們確實有助於大型圖形的效能。若要找出與節點 59 連接的所有圖形節點，請使用類似以下的查詢
WITH RECURSIVE nodes(x) AS ( SELECT 59 UNION SELECT aa FROM edge JOIN nodes ON bb=x UNION SELECT bb FROM edge JOIN nodes ON aa=x ) SELECT x FROM nodes;
在此情況下，initial-select 是簡單的查詢「SELECT 59」。這會建立基本情況。recursive-select 包含其他兩個 SELECT 陳述式。第一個遞迴 SELECT 會遵循 bb-to-aa 方向的邊界，而第二個遞迴 SELECT 會遵循 aa-to-bb 方向的邊界。UNION 會用於取代 UNION ALL，以防止遞迴進入無限迴圈（如果圖形包含週期）。
以下是針對有向圖形使用圖形查詢的實際範例：版本控制系統 (VCS) 通常會將專案的演進版本儲存在有向非循環圖形 (DAG) 中。將專案的每個版本稱為「簽入」。單一簽入可以有零個或多個父項。大多數簽入（除了第一個以外）只有一個父項，但在合併的情況下，簽入可能會擁有兩個、三個或更多個父項。用於追蹤簽入及其發生順序的架構可能會類似以下
CREATE TABLE checkin( id INTEGER PRIMARY KEY, mtime INTEGER -- timestamp when this checkin occurred ); CREATE TABLE derivedfrom( xfrom INTEGER NOT NULL REFERENCES checkin, -- parent checkin xto INTEGER NOT NULL REFERENCES checkin, -- derived checkin PRIMARY KEY(xfrom,xto) ); CREATE INDEX derivedfrom_back ON derivedfrom(xto,xfrom);
此圖形是非循環的。我們假設每個子簽入的 mtime 都不會小於其所有父項的 mtime。但與先前的範例不同，此圖形在任何兩個簽入之間可能有多條不同長度的路徑。
我們想要知道 checkin "@BASELINE" 在時間上最近的二十個祖先（在整個 DAG 中的數千個祖先中）。（Fossil VCS 使用類似這樣的查詢來顯示 checkin 的最近 N 個祖先。例如：https://www.sqlite.org/src/timeline?p=trunk&n=30。）
WITH RECURSIVE
ancestor(id,mtime) AS (
SELECT id, mtime FROM checkin WHERE id=@BASELINE
UNION
SELECT derivedfrom.xfrom, checkin.mtime
FROM ancestor, derivedfrom, checkin
WHERE ancestor.id=derivedfrom.xto
AND checkin.id=derivedfrom.xfrom
ORDER BY checkin.mtime DESC
LIMIT 20
)
SELECT * FROM checkin JOIN ancestor USING(id);
遞迴選擇中的「ORDER BY checkin.mtime DESC」術語透過防止它追蹤合併很久以前的 checkin 的分支，讓查詢執行速度快很多。ORDER BY 強制遞迴選擇專注於最近的 checkin，也就是我們想要的。如果遞迴選擇中沒有 ORDER BY，就必須計算數千個祖先的完整集合，然後依據 mtime 對它們全部進行排序，再取前二十個。ORDER BY 基本上會設定一個優先佇列，強制遞迴查詢先查看最近的祖先，允許使用 LIMIT 子句將查詢範圍限制在感興趣的 checkin 上。
遞迴選擇中的 ORDER BY 子句可用於控制樹狀結構的搜尋是深度優先還是廣度優先。為了說明，我們將使用上面範例中的「org」表格變形，沒有「height」欄位，並插入一些真實資料
CREATE TABLE org(
name TEXT PRIMARY KEY,
boss TEXT REFERENCES org
) WITHOUT ROWID;
INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');
以下是顯示樹狀結構以廣度優先模式的查詢
WITH RECURSIVE
under_alice(name,level) AS (
VALUES('Alice',0)
UNION ALL
SELECT org.name, under_alice.level+1
FROM org JOIN under_alice ON org.boss=under_alice.name
ORDER BY 2
)
SELECT substr('..........',1,level*3) || name FROM under_alice;
「ORDER BY 2」（與「ORDER BY under_alice.level+1」相同）會導致組織圖中較高層級（較小的「level」值）先處理，產生廣度優先搜尋。輸出為
Alice ...Bob ...Cindy ......Dave ......Emma ......Fred ......Gail
但是，如果我們將 ORDER BY 子句變更為加入「DESC」修飾詞，遞迴選擇會先處理組織中較低層級（較大的「level」值），產生深度優先搜尋
WITH RECURSIVE
under_alice(name,level) AS (
VALUES('Alice',0)
UNION ALL
SELECT org.name, under_alice.level+1
FROM org JOIN under_alice ON org.boss=under_alice.name
ORDER BY 2 DESC
)
SELECT substr('..........',1,level*3) || name FROM under_alice;
此已修改查詢的輸出為
Alice ...Bob ......Dave ......Emma ...Cindy ......Fred ......Gail
當遞迴選擇中省略 ORDER BY 子句時，佇列會表現為先進先出 (FIFO)，這會產生廣度優先搜尋。
以下查詢會計算 Mandelbrot 集合的近似值，並以 ASCII 藝術形式輸出結果
WITH RECURSIVE
xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
m(iter, cx, cy, x, y) AS (
SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
UNION ALL
SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
WHERE (x*x + y*y) < 4.0 AND iter<28
),
m2(iter, cx, cy) AS (
SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
),
a(t) AS (
SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
FROM m2 GROUP BY cy
)
SELECT group_concat(rtrim(t),x'0a') FROM a;
在此查詢中，「xaxis」和「yaxis」CTE 定義 Mandelbrot 集合將近似的點陣。在「m(iter,cx,cy,x,y)」CTE 中的每一列表示在「iter」次反覆運算後，從 cx、cy 開始的 Mandelbrot 反覆運算已到達點 x、y。此範例中的反覆運算次數限制為 28（這會嚴重限制運算解析度，但足以產生低解析度的 ASCII 藝術輸出）。「m2(iter,cx,cy)」CTE 會記錄從點 cx、cy 開始時達到的最大反覆運算次數。最後，「a(t)」CTE 中的每一列都會記錄一個字串，也就是輸出 ASCII 藝術的單一行。最後的 SELECT 陳述式只會查詢「a」CTE，以逐一擷取 ASCII 藝術的所有行。
在 SQLite 命令列殼層中執行上述查詢會產生以下輸出
....#
..#*..
..+####+.
.......+####.... +
..##+*##########+.++++
.+.##################+.
.............+###################+.+
..++..#.....*#####################+.
...+#######++#######################.
....+*################################.
#############################################...
....+*################################.
...+#######++#######################.
..++..#.....*#####################+.
.............+###################+.+
.+.##################+.
..##+*##########+.++++
.......+####.... +
..+####+.
..#*..
....#
+.
下一個查詢會解開數獨謎題。謎題狀態由 81 個字元的字串定義，這個字串是由從左到右、從上到下逐列讀取謎題方格中的輸入所組成。謎題中空白的方格會以「.」字元表示。因此輸入字串
53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79
對應到類似這樣的謎題
5 3 7 6 1 9 5 9 8 6 8 6 3 4 8 3 1 7 2 6 6 2 8 4 1 9 5 8 7 9
以下是解開謎題的查詢
WITH RECURSIVE
input(sud) AS (
VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
),
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT s FROM x WHERE ind=0;
「input」CTE 定義輸入謎題。「digits」CTE 定義一個包含 1 到 9 之間所有數字的表格。解開謎題的工作由「x」CTE 執行。x(s,ind) 中的輸入表示 81 個字元的字串「s」是一個有效的數獨謎題（沒有衝突），而且第一個未知字元在位置「ind」，或者如果所有字元位置都已填入，則 ind==0。因此，目標是計算「ind」為 0 的「x」輸入。
求解器透過新增「x」遞迴表格的新項目來運作。根據先前的項目，遞迴選擇會嘗試使用實際上可運作於該位置的 1 到 9 之間的所有值來填入一個新的位置。複雜的「NOT EXISTS」子查詢是找出每個候選「s」字串是否為有效的數獨謎題的關鍵。
最終答案是透過尋找 ind==0 的字串來找到。如果原始數獨問題沒有唯一的解，則查詢會傳回所有可能的解。如果原始問題無法解出，則不會傳回任何列。在此情況下，唯一的答案為
534678912672195348198342567859761423426853791713924856961537284287419635345286179
通用表格運算式的「AS MATERIALIZED」和「AS NOT MATERIALIZED」形式是非標準的 SQL 語法，從 PostgreSQL 複製而來。在 AS 關鍵字後使用 MATERIALIZED 或 NOT MATERIALIZED 會提供非約束提示給查詢規劃器，說明 CTE 應如何實作。
如果使用 MATERIALIZED 片語，則 select-stmt 會實體化為暫存於記憶體或暫時磁碟檔案中的暫存表格。然後，每當 CTE 表格名稱出現在後續 SQL 中時，就會使用該暫存表格取代 CTE 表格名稱。由於 select-stmt 會立即評估，因此會喪失套用最佳化（例如 查詢扁平化 或 下推最佳化）的機會。這種最佳化喪失是一種功能，而不是錯誤。開發人員可以使用 MATERIALIZED 關鍵字作為「最佳化圍欄」，以更嚴格地控制 SQLite 查詢規劃器的行為。SQLite 從 PostgreSQL 複製了使用 MATERIALIZED 作為最佳化圍欄的想法。
如果使用了 NOT MATERIALIZED 子句，則會將 select-stmt 替換為子查詢，以取代 CTE 表格名稱的每個出現。然後，將最佳化（例如 扁平化 和 下推）套用至子查詢，就好像子查詢已直接使用一樣。儘管其名稱為 NOT MATERIALIZED，但此子句並未禁止使用實體化。如果查詢規劃器認為實體化是最佳解決方案，它仍可以自由使用實體化來實作子查詢。NOT MATERIALIZED 的真正含義更接近於「視為任何一般檢視或子查詢」。
如果兩個提示都不存在，則 SQLite 可以自由選擇它認為最適合的實作策略。這是建議的方法。除非你有令人信服的理由，否則請勿對一般表格運算式使用 MATERIALIZED 或 NOT MATERIALIZED 關鍵字。
MATERIALIZED 和 NOT MATERIALIZED 提示僅在 SQLite 版本 3.35.0（2021-03-12）及更新版本中提供。
無法在 CREATE TRIGGER 中使用 WITH 子句。
WITH 子句必須出現在頂層 SELECT 陳述式的開頭或子查詢的開頭。無法將 WITH 子句加到 複合選取 的第二個或後續 SELECT 陳述式之前。
SQL:1999 規格要求 RECURSIVE 關鍵字在包含遞迴一般表格運算式的任何 WITH 子句中，必須緊接在 WITH 之後。但是，為了與 SqlServer 和 Oracle 相容，SQLite 沒有強制執行這項規則。
此頁面最後修改於 2024-01-29 11:00:27 UTC