当前位置： 首页 > article >正文

【MySQ】在MySQL里with 的用法

article 2024/9/22 11:20:37 
在MySQL中，WITH语句通常与公用表表达式（Common Table Expressions，简称CTE）一起使用。CTE是一种临时的结果集，类似于视图或子查询，它们在查询中被定义并且可以在一个或多个SELECT、INSERT、UPDATE或DELETE语句中被引用。

CTE通常用于简化复杂的查询，特别是那些包含多个子查询的查询。它们可以帮助提高查询的可读性和维护性。

在MySQL数据库中，WITH语句是一种用于在查询中临时定义一个可见的命名结果集的语法。它通常与SELECT语句一起使用，可以简化复杂查询，提高查询性能，以及提高查询语句的可读性。

1.with 语句的语法

在MySQL中，WITH语句的语法如下所示：

WITH
  cte_name1 (column_name1, column_name2, ...) AS (
    SELECT column_name1, column_name2, ...
    FROM table_name
    WHERE condition
  ),
  cte_name2 (column_name1, column_name2, ...) AS (
    SELECT column_name1, column_name2, ...
    FROM table_name
    WHERE condition
  )
SELECT column_name1, column_name2, ...
FROM cte_name1
JOIN cte_name2
WHERE condition;

SQL
其中，WITH关键字后的部分定义了一个或多个通用表表达式（CTE）。
每个CTE由一个名称（cte_name）
和一个查询（SELECT语句）组成。查询可以包含任意数目的列，也可以包含表连接、聚合函数等。

CTE定义后，可以在接下来的SELECT语句中引用它们的名称，并进行其他查询操作，如JOIN、WHERE等。

2.with 语句的应用场景

with 语句的主要优势在于可以避免多次编写相同的子查询，提高查询性能和可读性。通过使用with语句，可以将复杂的查询逻辑分解为多个可见的命名结果集，使查询更加直观清晰。

下面是一些with语句的常见应用场景：

2.1 递归查询

with语句非常适合处理递归查询。递归查询是指查询结果集包含对同一表的多次查询，每次查询都基于上一次查询的结果。在with语句中，可以使用递归的方式定义一个CTE，并在接下来的查询中引用它。

示例代码：

WITH RECURSIVE cte_name (column_name1, column_name2, ...) AS (
  -- 初始查询
  SELECT column_name1, column_name2, ...
  FROM table_name
  WHERE condition

  UNION ALL

  -- 递归查询
  SELECT column_name1, column_name2, ...
  FROM cte_name
  WHERE condition
)
SELECT * FROM cte_name;

2.2 数据转换和处理

使用with语句，可以在查询中先对数据进行转换和处理，然后再进行其他操作。这样可以提高查询的可读性和性能。

示例代码：

WITH cte_name (column_name1, column_name2, ...) AS (
  SELECT column_name1 * 2, column_name2, ...
  FROM table_name
  WHERE condition
)
SELECT SUM(column_name1), column_name2
FROM cte_name
GROUP BY column_name2;

2.3 复杂查询简化

当查询逻辑较为复杂时，使用with语句可以将查询分解为多个可见的结果集，使查询逻辑更加清晰。这样可以提高查询的可读性和维护性。

示例代码：

WITH
  sales_total (product_name, total_quantity) AS (
    SELECT product_name, SUM(quantity)
    FROM sales
    GROUP BY product_name
  ),
  sales_average (product_name, avg_quantity) AS (
    SELECT product_name, AVG(quantity)
    FROM sales
    GROUP BY product_name
  )
SELECT *
FROM sales_total
JOIN sales_average USING (product_name);

3. 注意事项和限制

在使用with语句时，需要注意以下几个方面：

* WITH语句中的每个CTE都只能在接下来的查询中使用一次，并且必须通过SELECT语句引用。
* CTE的名称在查询中是可见的，但不能在查询外部使用。
* WITH语句中的查询可以包含多个CTE，它们之间可以相互引用。
* WITH语句必须使用逗号进行分隔，最后一个CTE后不需要逗号。
* WITH语句中的CTE可以使用常规的查询语法，包括JOIN 、WHERE、GROUP BY等。
* MySQL对于WITH语句的支持并不是很完善，一些高级特性可能无法使用。

4.with语句的示例

为了更好地理解和演示WITH语句的用法，下面给出一个简单的示例。

假设有一个员工表employees，包含员工的ID、姓名、部门ID和工资等信息。同时还有一个部门表
departments，包含部门的ID和名称等信息。现在需要查询每个部门的平均工资和总工资，并按部门ID进行排序。

示例代码：

WITH
  avg_salary (department_id, average_salary) AS (
    SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id
  ),
  total_salary (department_id, total_salary) AS (
    SELECT department_id, SUM(salary)
    FROM employees
    GROUP BY department_id
  )
SELECT d.department_id, d.department_name, avg_salary.average_salary, total_salary.total_salary
FROM departments d
JOIN avg_salary ON d.department_id = avg_salary.department_id
JOIN total_salary ON d.department_id = total_salary.department_id
ORDER BY d.department_id;

以上示例中，首先通过两个CTE分别计算了每个部门的平均工资和总工资。然后使用
JOIN语句将部门表和这两个CTE进行连接，并按部门ID进行排序。

5. 总结

本文详细介绍了MySQL中WITH语句的语法、应用场景、注意事项和限制。

WITH语句可以提高查询的可读性和性能，尤其适用于处理递归查询、数据转换和处理以及简化复杂查询等情况。

在使用WITH语句时，需要注意每个CTE的命名和查询的语法，并理解其局限性。

6. with 语句示例

在MySQL中，`WITH`语句通常与公用表表达式（Common Table Expressions，简称CTE）一起使用。CTE是一种临时的结果集，类似于视图或子查询，它们在查询中被定义并且可以在一个或多个SELECT、INSERT、UPDATE或DELETE语句中被引用。

CTE通常用于简化复杂的查询，特别是那些包含多个子查询的查询。它们可以帮助提高查询的可读性和维护性。

以下是WITH语句的基本语法：

WITH cte_name (column1, column2, ...)
AS
(
    -- CTE的定义，可以是任何SELECT语句
    SELECT column1, column2, ...
    FROM ...
)
SELECT *
FROM cte_name;

例子1：简单的CTE

WITH EmployeeSalaries AS (
    SELECT
        employee_id,
        salary,
        department_id
    FROM
        employees
)
SELECT
    employee_id,
    salary
FROM
    EmployeeSalaries
WHERE
    department_id = 10;

在这个例子中，`EmployeeSalaries`是一个CTE，它从`employees`表中选择了员工ID、薪水和部门ID。然后，可以在随后的`SELECT`语句中引用这个CTE来获取特定部门的员工薪水信息。

例子2：使用CTE进行分区查询

WITH RankedEmployees AS (
    SELECT
        employee_id,
        salary,
        DEPARTMENT_ID,
        RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY salary DESC) AS salary_rank
    FROM
        employees
)
SELECT
    employee_id,
    salary,
    salary_rank
FROM
    RankedEmployees
WHERE
    salary_rank <= 3;

在这个例子中，`RankedEmployees`是一个CTE，它使用窗口函数`RANK()`来为每个部门的员工薪水进行排名。然后，可以从这个CTE中选择每个部门薪水排名前三的员工。

例子3：递归CTE

CTE也可以是递归的，这意味着CTE可以引用自身。这在处理层次或递归数据结构时非常有用，比如组织架构、物料清单等。

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT
        employee_id,
        manager_id,
        employee_name
    FROM
        employees
    WHERE
        manager_id IS NULL
    UNION ALL
    SELECT
        e.employee_id,
        e.manager_id,
        e.employee_name
    FROM
        employees e
    INNER JOIN EmployeeHierarchy eh ON eh.employee_id = e.manager_id
)
SELECT
    employee_id,
    manager_id,
    employee_name
FROM
    EmployeeHierarchy;

在这个递归CTE的例子中，`EmployeeHierarchy`首先选择了所有没有经理的员工（即顶级经理），然后递归地联接`employees`表，将每个员工与他们的直接下属相连接。

CTE可以在一个查询中被定义一次，并且在查询的多个部分中被引用多次，这样可以避免复杂的子查询并且提高性能。

7.如何使用CTE进行数据的分组和聚合操作？

公用表表达式（CTE）可以与数据的分组和聚合操作一起使用，以简化复杂的查询。CTE允许你在查询的顶部定义一个临时的结果集，这个结果集可以包含分组和聚合操作，然后在主查询中引用这个结果集。

以下是一个使用CTE进行数据分组和聚合操作的例子：

假设我们有一个名为`sales`的表，包含以下列：`sale_id`（销售ID）、`product_id`（产品ID）、`quantity`（数量）和`sale_date`（销售日期）。我们想要计算每个产品每月的销售总量。

首先，我们可以创建一个CTE来提取每个月的销售记录：

WITH MonthlySales AS (
    SELECT
        product_id,
        SUM(quantity) AS total_quantity,
        YEAR(sale_date) AS sale_year,
        MONTH(sale_date) AS sale_month
    FROM
        sales
    GROUP BY
        product_id,
        sale_year,
        sale_month
)
SELECT
    product_id,
    sale_year,
    sale_month,
    total_quantity
FROM
    MonthlySales
ORDER BY
    product_id,
    sale_year,
    sale_month;

在这个例子中，`MonthlySales` CTE使用了`GROUP BY`子句来按`product_id`、`sale_year`和`sale_month`进行分组，并计算每个分组的总销售数量。然后，我们可以在主查询中选择这些分组的结果，并按产品ID、销售年份和月份排序。

如果你想在一个查询中获取所有产品的销售总量，你可以在CTE中包含一个总计分组：

WITH TotalSales AS (
    SELECT
        product_id,
        YEAR(sale_date) AS sale_year,
        MONTH(sale_date) AS sale_month,
        SUM(quantity) AS total_quantity
    FROM
        sales
    GROUP BY
        product_id,
        sale_year,
        sale_month
),
OverallTotalSales AS (
    SELECT
        YEAR(sale_date) AS sale_year,
        MONTH(sale_date) AS sale_month,
        SUM(total_quantity) AS overall_total_quantity
    FROM
        TotalSales
    GROUP BY
        sale_year,
        sale_month
)
SELECT
    product_id,
    sale_year,
    sale_month,
    total_quantity
FROM
    TotalSales
UNION ALL
SELECT
    NULL AS product_id,
    sale_year,
    sale_month,
    overall_total_quantity
FROM
    OverallTotalSales
ORDER BY
    sale_year,
    sale_month,
    product_id;

在这个例子中，`TotalSales` CTE计算了每个产品的每月销售总量，而`OverallTotalSales` CTE进一步计算了所有产品的每月销售总量。然后，我们使用`UNION ALL`将这两个结果集合并在一起，并按年份和月份排序。

CTE可以包含多个层次，每个层次都可以包含分组和聚合操作，这使得它们非常适合于复杂的数据分析任务。

8. 如何使用CTE进行更复杂的数据聚合，比如计算不同时间段的总销售额？

使用CTE（公用表表达式）进行复杂数据聚合的一个常见场景是计算不同时间段的总销售额。这可能涉及到多个表的联接、多级分组、条件过滤以及聚合函数的使用。以下是一个示例，展示了如何使用CTE来计算每个销售员在不同时间段（如每月）的总销售额。

假设我们有两个表：`sales`和`employees`。`sales`表包含销售记录，包括`sale_id`（销售ID）、`employee_id`（销售员ID）、`sale_amount`（销售金额）和`sale_date`（销售日期）。`employees`表包含销售员的信息，包括`employee_id`（销售员ID）和`employee_name`（销售员姓名）。

我们的目标是计算每个销售员每月的总销售额。

WITH SalesDetails AS (
    SELECT
        e.employee_id,
        e.employee_name,
        YEAR(s.sale_date) AS sale_year,
        MONTH(s.sale_date) AS sale_month,
        s.sale_amount
    FROM
        sales s
    JOIN employees e ON s.employee_id = e.employee_id
),
MonthlySales AS (
    SELECT
        employee_id,
        employee_name,
        sale_year,
        sale_month,
        SUM(sale_amount) AS total_sales
    FROM
        SalesDetails
    GROUP BY
        employee_id,
        sale_year,
        sale_month
)
SELECT
    employee_id,
    employee_name,
    sale_year,
    sale_month,
    total_sales
FROM
    MonthlySales
ORDER BY
    employee_id,
    sale_year,
    sale_month;

在这个示例中：

  1. SalesDetails CTE：首先，我们创建了一个SalesDetails CTE，它通过联接sales和employees表来获取每个销售记录的销售员ID、销售员姓名、销售日期和销售金额。同时，我们使用YEAR()和MONTH()函数来提取销售日期的年份和月份。

  2. MonthlySales CTE：接下来，我们创建了一个MonthlySales CTE，它对SalesDetails的结果按销售员ID、年份和月份进行分组，并使用SUM()聚合函数来计算每个分组的总销售额。

  3. 主查询：最后，我们从MonthlySales CTE中选择数据，并按销售员ID、年份和月份排序，以展示每个销售员每月的总销售额。

这个示例展示了如何使用CTE进行多级聚合操作，以及如何结合联接、分组和聚合函数来解决复杂的数据分析问题。通过将复杂的查询逻辑分解到多个CTE中，我们可以提高查询的可读性和可维护性。

9. 如何使用CTE进行数据清洗，比如去除重复的销售记录？

使用CTE（公用表表达式）进行数据清洗，比如去除重复的销售记录，可以帮助你组织和简化查询。以下是一个示例，展示了如何使用CTE来去除重复的销售记录。

假设我们有一个名为sales的表，包含以下列：sale_id（销售ID）、employee_id（销售员ID）、customer_id（客户ID）、sale_amount（销售金额）和sale_date（销售日期）。有时，由于数据导入错误或其他原因，表中可能包含重复的销售记录。

我们的目标是去除重复的销售记录，并保留一个唯一的销售记录。

WITH RankedSales AS (
    SELECT
        sale_id,
        employee_id,
        customer_id,
        sale_amount,
        sale_date,
        ROW_NUMBER() OVER (PARTITION BY employee_id, customer_id, sale_date ORDER BY sale_id) AS rn
    FROM
        sales
)
SELECT
    sale_id,
    employee_id,
    customer_id,
    sale_amount,
    sale_date
FROM
    RankedSales
WHERE
    rn = 1;

在这个示例中：

  1. RankedSales CTE：首先，我们创建了一个RankedSales CTE，它使用ROW_NUMBER()窗口函数为每个销售记录分配一个唯一的行号。PARTITION BY子句用于指定要检查重复记录的列（在这个例子中是employee_id、customer_id和sale_date）。ORDER BY子句用于确定记录的排序，以便为每个分组的第一个记录分配行号1。

  2. 主查询：然后，在主查询中，我们从RankedSales CTE中选择行号为1的记录，即每个分组的第一个记录。这样，我们就可以去除重复的销售记录，只保留每个分组中的第一个记录。

这种方法的优点是它允许你保留每个重复组中的一个记录，同时去除其他重复的记录。此外，通过调整ROW_NUMBER()函数中的ORDER BY子句，你可以控制哪个记录被视为“第一个”并被保留。

请注意，如果你想要保留每个重复组中的最后一个记录，你可以使用ROW_NUMBER()函数并更改ORDER BY子句，或者使用RANK()或DENSE_RANK()函数来处理并列情况。如果你想要删除所有重复的记录，可以使用DISTINCT关键字或GROUP BY子句来聚合数据。

10. 如何使用CTE来处理数据中的异常值？

使用CTE（公用表表达式）处理数据中的异常值是一种有效的策略，可以帮助你在执行聚合或分析之前清理数据集。异常值可以是错误数据、意外噪声或不符合数据模式的值。以下是使用CTE来识别和处理异常值的一些常见方法：

  1. 定义异常值的标准

首先，你需要定义什么是异常值。这可能基于业务逻辑、统计分析（如标准差、四分位数）或其他规则。

  1. 创建CTE以识别异常值

你可以在CTE中使用条件语句或比较操作来标识异常值。

示例： 假设你有一个销售数据表sales，包含sale_amount（销售金额）和sale_date（销售日期）。如果销售金额超过某个阈值（比如平均销售额的3倍标准差），则认为该记录是异常的。

WITH SalesStats AS (
    SELECT
        AVG(sale_amount) AS avg_sale,
        STDDEV(sale_amount) AS std_deviation
    FROM
        sales
),
AnomalousSales AS (
    SELECT
        s.*,
        (s.sale_amount > (SELECT avg_sale FROM SalesStats) * 3 OR s.sale_amount < (SELECT avg_sale FROM SalesStats) / 3) AS is_anomalous
    FROM
        sales s
)
SELECT
    *
FROM
    AnomalousSales
WHERE
    is_anomalous = 1;

在这个例子中，SalesStats CTE 计算了所有销售金额的平均值和标准差。AnomalousSales CTE 则标记了那些销售金额超过平均值的3倍标准差的记录为异常。

  1. 过滤异常值

在主查询中，你可以使用CTE的结果来过滤掉异常值，只处理正常数据。

SELECT
    sale_amount,
    sale_date
FROM
    AnomalousSales
WHERE
    is_anomalous = 0;
  1. 使用窗口函数进行比较

如果你需要基于相对标准（如与同一组内的其他值比较）来识别异常值，可以使用窗口函数。

示例： 识别每个销售员销售额中的异常值。

WITH SalesPerEmployee AS (
    SELECT
        employee_id,
        sale_amount,
        RANK() OVER (PARTITION BY employee_id ORDER BY sale_amount DESC) AS sale_rank
    FROM
        sales
)
SELECT
    employee_id,
    sale_amount
FROM
    SalesPerEmployee
WHERE
    sale_rank = 1 OR sale_rank = 2; -- 假设前两个最高销售额为异常
  1. 多级CTE处理

对于更复杂的异常检测逻辑，可能需要多级CTE，每级处理不同的方面，最终组合结果。

示例： 组合使用多个CTE来处理和分析异常。

WITH RawSales AS (
    SELECT
        employee_id,
        customer_id,
        sale_amount,
        sale_date
    FROM
        sales
),
FilteredSales AS (
    SELECT
        employee_id,
        customer_id,
        sale_amount,
        sale_date
    FROM
        RawSales
    WHERE
        sale_amount BETWEEN 100 AND 10000 -- 基于业务规则过滤
),
AnalyzedSales AS (
    SELECT
        employee_id,
        customer_id,
        sale_amount,
        sale_date,
        (s.sale_amount > (SELECT AVG(sale_amount) * 3 FROM FilteredSales WHERE employee_id = s.employee_id)) AS is_anomalous
    FROM
        FilteredSales s
)
SELECT
    employee_id,
    customer_id,
    sale_amount,
    sale_date
FROM
    AnalyzedSales
WHERE
    is_anomalous = 0;

在这个例子中，我们首先过滤掉明显不合理的数据，然后进一步分析每个销售员的销售额，以识别异常值。

使用CTE处理异常值可以提高查询的可读性和组织性，使得数据处理步骤更加清晰。


http://www.kler.cn/news/314586.html

相关文章：

  • 【技术解析】消息中间件MQ：从原理到RabbitMQ实战(深入浅出)
  • 计算机毕业设计之：基于微信小程序的校园流浪猫收养系统（源码+文档+讲解）
  • WEB 编程：富文本编辑器 Quill 配合 Pico.css 样式被影响的问题
  • vue配置axios
  • 使用Java实现高效用户行为监控系统
  • 二叉树（二）深度遍历和广度遍历
  • Redis的三种持久化方法详解
  • Spring Boot实战：使用策略模式优化商品推荐系统
  • linux用户管理运行级别找回root密码
  • 【Java注解】
  • Docker指令学习1
  • 【Kubernetes】常见面试题汇总（二十七）
  • 【网络安全 | 代码审计】PHP无参数RCE
  • 从图像处理到字符识别：基于STM32与C语言的车牌识别系统实现
  • HarmonyOS开发者基础认证考试试题
  • 基于mockito做单元测试
  • 16【Protues51单片机仿真】智能洗衣机倒计时系统
  • 【如何在 Windows 10 主机上通过 VMware 安装 Windows 11 虚拟机，并共享主机网络】
  • ftp服务的管理及安全优化
  • Google 扩展 Chrome 安全和隐私功能
  • C/C++通过CLion2024进行Linux远程开发保姆级教学
  • io多路复用：epoll水平触发（LT）和边沿触发（ET）的区别和优缺点
  • Linux 自旋锁
  • Spring Mybatis 动态语句 总结
  • 简单生活的快乐
  • （k8s）kubernetes集群基于Containerd部署
  • Flask-SQLAlchemy一对多 一对一 多对多关联
  • GDPU Andriod移动应用 Activity
  • 【数据结构与算法】LeetCode：哈希表
  • Alinx MPSoC驱动开发第17章I2C实验修改设备树后petalinux编译报错