SQL中的WITH语句：公共表达式CTE，用作临时视图或子查询的定义方式，创建临时的结果集

admin

2024年4月10日 0:4 本文热度 560

在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管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。