[進階SQL]With As進行子查詢(CTE)[SQL-004]
本篇整理SQL CTE 一般資料表運算式之定義與操作。
先前SQL筆記有:
第一篇為入門指令,包含如何選取欄位、表格、排序、更新。
第二篇為運算指令,包含最大最小/計算個數、平均等等。
第三篇為集合運算指令 (JOIN)。
其他則有我玩HackerRank的紀錄分享。
CTE 一般資料表運算式(Common Table Expression)
在SQL的世界中,我們經常需要進行多表的取用,因此會使用子查詢(subquery),但為了簡化子查詢的易讀性,與達成遞迴查詢,因而有CTE的寫法,透過CTE暫存資料來讓query整體好讀。
CTE 是一個「暫存」的結果集合,透過暫時儲存 AS 括號中的 Query 結果。
CTE定義
SQL Server 與 MySQL 對CTE的定義是,CET本身就是一個根據命名的暫存表,只存在於進行單一SQL query中,並只有在query執行階段中持續。
與 derived table相比,CTE提供更好的效能與易讀性。
# SQL Server
WITH expression_name[(column_name [,...])]
AS
(CTE_definition)
SQL_statement;# My SQL
WITH cte_name (column_list)
AS (query) SELECT * FROM cte_name;
expression_name
為自定義表達式名稱,並將取出的欄位(column)命名。CTE_definition
相當於子查詢進行的事情。- 最終以
SQL_statement
進行資料查詢 (例如 SQL入門的SELECT
,UPDATE
)。
舉個例子來說,
如果有業務人員與銷售訂單資料,我們就可以透過CTE預先進行暫存,再進行業務人員年度的銷售訂單數目的查詢(query)就如:
# 建立CTE 表達式與欄位名稱
WITH cte_sales_amounts (staff, sales, year) # 定義CTE query要查詢的暫存內容
AS
(
SELECT
first_name + ' ' + last_name,
SUM(quantity * list_price * (1 - discount)),
YEAR(order_date)
FROM
sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN sales.staffs s ON s.staff_id = o.staff_id
)# 執行根據CTE欄位的query
SELECT staff, sales
FROM cte_sales_amounts
WHERE year = 2018;
定義好的CTE就如暫存表,讓我們在往後進行查詢時,可以簡單透過修改SQL_statement
來改變要進行查詢的資料。
操作:
- 多個CTE中間用逗號(,)分隔。
- 可以使用遞迴CTE來表示階層。
CTE的限制
- CTE後面必須直接使用CTE資料行(column)的單一expression。例如:
SELECT
、INSERT
、UPDATE
、DELETE
。 - CTE可以引用自身,也可以引用在同一
WITH
子句中定義的 CTE,但有順序性(不可往前參考)。 CTE_definition
不可使用ORDER BY
,INTO
,OPTION
,FOR BROWSE
等子句。
更多內容可以依據使用的 SQL語言找官方文件參閱,例如微軟 T-SQL進行CTE定義、操作、限制與範例說明 )。
或是相關的操作影片,也可以從youtube找到專家的分享:
感謝收看 ^^