MySQL with语法详解

什么是with语法

在MySQL中，WITH语法是一种常用的查询语法，它能够创建临时命名的结果集，并在后续的查询中使用这些结果集。

通常情况下，我们在一个查询语句中只能使用已经存在的表或视图进行操作，但是有时候我们需要对查询的结果进行进一步的操作，这时候就可以使用WITH语法创建一个临时的结果集，然后在后续的查询中使用这个结果集。

WITH语法可以让我们更方便地对复杂的查询进行组织和管理，提高查询的可读性和可维护性。

WITH语法的基本用法

WITH语法的基本格式如下：

WITH
    result_name1 AS (
        -- 第一个结果集的查询语句
    ),
    result_name2 AS (
        -- 第二个结果集的查询语句
    ),
    ...
SELECT
    ...
FROM
    ...

WITH关键字后面可以跟一个或多个用逗号分隔的结果集定义，每个结果集定义包含一个结果集名称和一个查询语句。

在查询中，可以使用result_name1、result_name2等结果集名称来引用对应的结果集。这些结果集在查询中的作用和普通的表或视图一样，在查询中可以被引用、过滤、排序等。

WITH语法的示例

为了更好地理解WITH语法的用法，我们来看几个示例。

示例一：计算员工的平均工资

假设我们有一个名为employee的表，包含了员工的姓名和薪水信息。我们想要计算员工的平均工资，并返回高于平均工资的员工姓名和薪水。

首先，我们可以使用下面的语句计算平均工资：

WITH
    avg_salary AS (
        SELECT AVG(salary) AS avg_salary FROM employee
    )
SELECT
    name, salary
FROM
    employee
WHERE
    salary > (SELECT avg_salary FROM avg_salary)

在这个查询中，我们先使用WITH语法创建了一个名为avg_salary的结果集，其中包含了计算平均工资的查询语句。然后，在主查询中，我们使用avg_salary的结果来过滤出高于平均工资的员工。

示例二：计算商品分类的销售额和利润

假设我们有一个名为product的表，包含了商品的名称、分类、销售额和利润信息。我们想要计算每个商品分类的销售额和利润，并按照销售额和利润的降序排列。

我们可以使用下面的语句实现：

WITH
    sales AS (
        SELECT category, SUM(sales) AS total_sales FROM product GROUP BY category
    ),
    profits AS (
        SELECT category, SUM(profit) AS total_profits FROM product GROUP BY category
    )
SELECT
    s.category, s.total_sales, p.total_profits
FROM
    sales s
JOIN
    profits p ON s.category = p.category
ORDER BY
    s.total_sales DESC, p.total_profits DESC

在这个查询中，我们使用了两个WITH语句分别创建了名为sales和profits的结果集，其中sales结果集用于计算商品分类的销售额，profits结果集用于计算商品分类的利润。然后，在主查询中，我们使用sales和profits的结果集来进行连接操作，并按照销售额和利润的降序排列。