&

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

admin

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

在SQL中，WITH語句通常被用作一種臨時視圖或子查詢的定義方式，可以創(chuàng)建臨時的結果集，這些結果集可以在主查詢中被引用。這種結構也被稱為公共表達式（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是之前創(chuàng)建的數(shù)據(jù)庫。數(shù)據(jù)表示例數(shù)據(jù)分別如下：

data_learning.product_order（商品銷量表）：

data_learning.product（商品信息表）：

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

具體的創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表的SQL語句可以查看我之前的文章。

SQL創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表
數(shù)據(jù)分析師的日常，公眾號：數(shù)據(jù)分析師的日常SQL創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表

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

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

比如

問題：查詢商品數(shù)量超過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            ;

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

2.2 示例2、分解多業(yè)務步驟邏輯

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

with tmp1 as(            -- 首先找出銷量超過2000的商品類別            SELECT            category_id            ,sales_volume            FROM            data_learning.product_order            WHERE            sales_volume > 2000            ),            tmp2 as (            -- 接著找出屬于這些商品類別的產(chǎn)品名稱及價格、銷量            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語句在分解多業(yè)務步驟邏輯中的應用。

2.3 示例3、數(shù)據(jù)清洗

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

以下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語句在數(shù)據(jù)分析中非常有用，上述僅僅舉了幾個我在實際工作中比較常用的一些應用場景，實際上其應用場景遠不止這些，也歡迎朋友們一起交流自己在實際工作中都如何使用WITH語句。

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

關鍵字查詢

方式

臨時

sql

創(chuàng)建

正在查詢...

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

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

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

點晴免費OA是一款軟件和通用服務都免費，不限功能、不限時間、不限用戶的免費OA協(xié)同辦公管理系統(tǒng)。