,,

SQL中的WITH語句：公共表達式CTE，用作臨時視圖或子查詢的定義方式，創建臨時的結果集

admin

2024年4月10日 0:4 本文熱度 1279

在SQL中，WITH語句通常被用作一種臨時視圖或子查詢的定義方式，可以創建臨時的結果集，這些結果集可以在主查詢中被引用。這種結構也被稱為公共表達式（CTE：Common Table Expressions）。

01 語法結構

WITH tmp_name AS (            SELECT column1, column2, ...            FROM table_name            WHERE condition            )            SELECT ...            FROM tmp_name            WHERE condition;

02 應用示例

這里通過表data_learning.product_order（商品銷量表）、data_learning.product（商品信息表）、data_learning.product_category（商品二級分類信息表）進行舉例，data_learning是之前創建的數據庫。數據表示例數據分別如下：

data_learning.product_order（商品銷量表）：

data_learning.product（商品信息表）：

data_learning.product_category（商品二級分類信息表）：

具體的創建數據庫和數據表的SQL語句可以查看我之前的文章。

SQL創建數據庫和數據表
數據分析師的日常，公眾號：數據分析師的日常SQL創建數據庫和數據表

2.1 示例1、封裝和重用臨時表

通過WITH語句，我們可以封裝復雜的子查詢或視圖，使其可重用。這樣可以提高查詢的可讀性和可維護性。例如，假設我們需要在多個查詢中使用某個常用的邏輯操作。使用WITH語句，我們可以將該邏輯操作封裝為一個臨時表，并在需要的地方重復使用。

比如

問題：查詢商品數量超過5的商品類別，常見的SQL如下：

SELECT                b.category_name                ,COUNT(DISTINCT a.product_id) as product_cnt            FROM                data_learning.product_order a            LEFT JOIN                 data_learning.product_category b             ON a.category_id = b.category_id            GROUP BY 1            HAVING COUNT(DISTINCT a.product_id) > 5            ORDER BY 2 DESC            ;

使用with語句可以轉換為：

with tmp as(            SELECT                b.category_name                ,COUNT(DISTINCT a.product_id) as product_cnt            FROM                data_learning.product_order a            LEFT JOIN                 data_learning.product_category b             ON a.category_id = b.category_id            GROUP BY 1            )            SELECT            category_name            ,product_cnt            FROM            tmp            WHERE product_cnt > 5            ORDER BY 2 DESC            ;

在實際工作中，在涉及到要使用多個表的多個業務時間字段時，我會先使用with語句建一個臨時結果集，再寫其他查詢語句。當然，有建表權限的話可以直接建一個臨時表，再做查詢。

2.2 示例2、分解多業務步驟邏輯

復雜的業務邏輯可能需要多個步驟來計算最終結果。使用WITH語句可以使這些步驟更清晰。問題：查詢銷量超過2000的商品類別，這個類別有哪些產品及對應的產品價格和銷量時多少。

with tmp1 as(            -- 首先找出銷量超過2000的商品類別            SELECT            category_id            ,sales_volume            FROM            data_learning.product_order            WHERE            sales_volume > 2000            ),            tmp2 as (            -- 接著找出屬于這些商品類別的產品名稱及價格、銷量            SELECT              a.category_id              ,a.product_id              ,a.sales_volume              ,b.product_name              ,b.price            FROM              data_learning.product_order a            LEFT JOIN                data_learning.product b            ON a.product_id = b.product_id            WHERE              a.category_id IN (SELECT category_id FROM tmp1)            )            -- 從最終的表中選擇結果            SELECT              *            FROM              tmp2            ;

實際工作中涉及到的情況比這個步驟可能更多，此示例僅供理解WITH語句在分解多業務步驟邏輯中的應用。

2.3 示例3、數據清洗

WITH語句也可以用來進行數據清洗，例如以下查詢將刪除重復的數據。

以下SQL語句僅供理解使用。

WITH CTE AS            (            SELECT               *,              ROW_NUMBER() OVER (PARTITION BY duplicate_column ORDER BY duplicate_column) AS row_num            FROM your_table            )            DELETE FROM CTE            WHERE row_num > 1;

WITH語句在數據分析中非常有用，上述僅僅舉了幾個我在實際工作中比較常用的一些應用場景，實際上其應用場景遠不止這些，也歡迎朋友們一起交流自己在實際工作中都如何使用WITH語句。

該文章在 2024/4/10 0:05:43 編輯過

關鍵字查詢

方式

臨時

sql

創建

正在查詢...

點晴ERP是一款針對中小制造業的專業生產管理軟件系統,系統成熟度和易用性得到了國內大量中小企業的青睞。

點晴PMS碼頭管理系統主要針對港口碼頭集裝箱與散貨日常運作、調度、堆場、車隊、財務費用、相關報表等業務管理，結合碼頭的業務特點，圍繞調度、堆場作業而開發的。集技術的先進性、管理的有效性于一體，是物流碼頭及其他港口類企業的高效ERP管理信息系統。

點晴WMS倉儲管理系統提供了貨物產品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質期管理,貨位管理,庫位管理,生產管理,WMS管理系統,標簽打印,條形碼,二維碼管理,批號管理軟件。