深入了解With SQL

With SQL，即通用表表達式（Common Table Expressions），是一種用於定義帶有命名查詢的臨時表格的語法格式。With SQL 結構類似於子查詢，但比起子查詢，它更易於理解、維護和重用。在本文中，我們將以多個方面來深入探討 With SQL 的使用方法和優勢。

一、簡介

With SQL 或稱為 CTE（Common Table Expressions），是標準 SQL 中一個非常強大且十分實用的特性。它允許我們在 SQL 查詢中使用諸如 SELECT、INSERT、DELETE 和 UPDATE 等操作。可類比編程語言中的變量，With SQL 在 SQL 查詢過程中起到了變量的作用，減少了多次複雜查詢重複操作，同時也提高了查詢的性能。

CTE 可以在 WITH 子句中定義多個表，這些表的名稱和定義與查詢的其餘部分分開。常見的使用場景包括：遞歸查詢，過濾重複數據，使用窗口函數進行排序。

WITH employees_working_in_USA AS (
  SELECT * 
  FROM employees 
  JOIN departments ON employees.department_id = departments.department_id
  WHERE departments.country = 'USA'
)
SELECT * FROM employees_working_in_USA;

上述示例展示了一個簡單的 With SQL 查詢。該查詢首先創建一個名為 employees_working_in_USA 的臨時表格，其中存儲了來自 employees 表和 departments 表的所有員工和其工作部門信息，且只選擇部門在美國的員工。然後，該查詢從 employees_working_in_USA 表中選擇所有列。

二、新增列、表或子查詢

With SQL 查詢中，我們不僅可以選擇常規查詢的列，還可以添加新的列或子查詢。這使我們可以在查詢中計算一些值或使用其他更高級別的查詢。

WITH revenue AS (
  SELECT order_id, (quantity * price) AS order_revenue
  FROM order_details
)
SELECT MAX(order_revenue)
FROM revenue;

上述示例展示了一個 With SQL 查詢，具有從 order_details 表計算總收入的計算列（即使用 quantity 和 price 計算 order_revenue）。這種計算列可以被其他查詢繼續使用。

With SQL 還可以被用作子查詢的一部分。這意味着您可以從一個 With SQL 查詢中選擇並連接數據，而不必為每個子查詢重複執行相同的工作。

WITH high_sales AS (
    SELECT region, SUM(sales) AS total_sales
    FROM sales
    GROUP BY region
    HAVING SUM(sales) >= 1000000
)
SELECT * FROM employees 
WHERE department_id IN (
    SELECT department_id FROM high_sales
);

三、遞歸查詢

遞歸查詢是 With SQL 最為重要的應用之一。遞歸查詢主要是針對有父子結構關係的數據表進行處理，並且對於需要多次遞歸訪問相同表格的操作，使用 With SQL 可以減少重複代碼並提高性能。

WITH RECURSIVE tree(id, name, parent_id) AS (
    SELECT id, name, parent_id
    FROM menu
    WHERE name = 'Home'
  UNION ALL
    SELECT m.id, m.name, m.parent_id
    FROM menu m
    INNER JOIN tree t ON m.parent_id = t.id
)
SELECT * FROM tree;

上述示例中，我們使用了遞歸查詢來獲取“Home”節點的所有子節點信息，這裡的 menu 表必須具有自引用關係，即有父節點和子節點的關聯。查詢首先從 menu 表中選出根節點 name=’Home’ 的項，然後使用 union all 遞歸地加入所有子項，直到完全遍歷完所有子節點。

四、使用連接數據

With SQL 還可以被用作連接數據的一種方式。下面是一個使用 With SQL 和 INNER JOIN 語句來連接兩個數據表的查詢示例：

WITH ActiveOrders AS (
    SELECT order_id
    FROM orders
    WHERE order_status = 'Active'
)
SELECT *
FROM customers c
INNER JOIN ActiveOrders o ON c.customer_id = o.customer_id;

上述示例中，我們先創建一個名為 ActiveOrders 的 With SQL 查詢，它選擇所有狀態為“Active”的訂單。然後，我們將其與 customers 表格 INNER JOIN 在一起，以檢索所有客戶和其關聯的活動訂單。

總結

With SQL 是 SQL 查詢中非常實用的特性之一。使用 With SQL 可以減少在代碼中的冗餘，同時提高查詢的可讀性和性能。本文介紹了幾個使用 With SQL 的示例，您可以根據需求和場景進行靈活運用。