[進階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的限制

1. CTE後面必須直接使用CTE資料行(column)的單一expression。例如： SELECT、INSERT、UPDATE 、DELETE。
2. CTE可以引用自身，也可以引用在同一 WITH子句中定義的 CTE，但有順序性(不可往前參考)。
3. CTE_definition不可使用ORDER BY,INTO,OPTION,FOR BROWSE 等子句。

更多內容可以依據使用的 SQL語言找官方文件參閱，例如微軟 T-SQL進行CTE定義、操作、限制與範例說明 )。

或是相關的操作影片，也可以從youtube找到專家的分享：

感謝收看 ^^