mysql中with as用法

在MySQL中，WITH子句用于创建临时结果集，这些结果集可以在SELECT、INSERT、UPDATE或DELETE语句中使用。通过使用WITH子句，可以简化复杂的查询，提高代码的可读性和可维护性。

MySQL中的WITH语句用于定义一个临时的结果集，这个结果集可以在后续的查询中被引用，WITH语句的基本语法如下：

WITH temp_table AS (
  -子查询
)
SELECT ...
FROM temp_table
WHERE ...

在这个语法中，temp_table是一个临时的结果集的名称，可以自定义，子查询是用于生成临时结果集的查询语句，在主查询中，可以使用SELECT ... FROM temp_table来引用临时结果集中的数据。

下面是一个使用WITH语句的示例：

WITH sales_summary AS (
  SELECT product_id, SUM(quantity) AS total_quantity, SUM(price) AS total_price
  FROM sales
  GROUP BY product_id
)
SELECT p.product_name, s.total_quantity, s.total_price
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id
ORDER BY s.total_quantity DESC;

在这个示例中，首先使用WITH语句定义了一个名为sales_summary的临时结果集，该结果集包含了每个产品的销售总量和总价，在主查询中，通过JOIN操作将产品表和临时结果集关联起来，最后按照销售总量降序排列。

使用WITH语句的好处是可以简化复杂的查询逻辑，将子查询的结果集定义为一个临时表，然后在主查询中多次引用，这样可以避免重复执行相同的子查询，提高查询性能。

除了基本的用法外，WITH语句还支持以下扩展特性：

1、公共表表达式（CTE）：WITH语句可以定义多个临时结果集，这些结果集之间可以相互引用，这种方式被称为公共表表达式（CTE）。

“`sql

WITH sales_summary AS (

SELECT product_id, SUM(quantity) AS total_quantity, SUM(price) AS total_price

FROM sales

GROUP BY product_id

),

top_products AS (

SELECT product_id, total_quantity, total_price, RANK() OVER (ORDER BY total_quantity DESC) AS rank

FROM sales_summary

)

SELECT * FROM top_products WHERE rank <= 5;

“`

在这个示例中，首先定义了一个名为sales_summary的临时结果集，然后定义了一个名为top_products的临时结果集，该结果集包含了每个产品的销售总量、总价以及排名，在主查询中选择了排名前五的产品。

2、递归CTE：WITH语句还可以定义递归的临时结果集，用于解决树形结构的问题。

“`sql

WITH tree AS (

SELECT id, name, parent_id, 1 AS level

FROM categories

WHERE parent_id IS NULL

UNION ALL

SELECT c.id, c.name, c.parent_id, t.level + 1 AS level

FROM categories c

JOIN tree t ON c.parent_id = t.id

)

SELECT * FROM tree;

“`

在这个示例中，首先定义了一个名为tree的临时结果集，该结果集包含了分类表中的所有分类信息，并计算了每个分类的层级，通过递归的方式将子分类与父分类关联起来，选择所有的分类信息。

3、标量子查询：WITH语句中的子查询可以使用标量子查询，即在子查询中使用聚合函数或DISTINCT关键字。

“`sql

WITH unique_sales AS (

SELECT DISTINCT product_id, price, quantity

FROM sales

)

SELECT * FROM unique_sales;

“`

在这个示例中，首先定义了一个名为unique_sales的临时结果集，该结果集包含了销售表中不重复的产品ID、价格和数量信息，选择所有的不重复的销售信息。