1. 概述
`WITH AS` 是一种 SQL 语句,用于创建临时表并在后续查询中使用它们。通常,我们将其称为公共表表达式或CTE。
在MySQL中,`WITH AS` 跟通用表表达式(CTE)非常相似,允许SQL查询复杂数据集的子查询来与外部查询结合。
`WITH AS` 能够优化查询,在某些情况下,提升查询性能。
##2. 描述 `WITH AS` 的使用方法
下面是一些使用 `WITH AS` 格式的示例语句,它使用了MySQL:
###2.1 基本使用方法
我们在SELECT语句的开头使用WITH AS关键字来定义公共表表达式,后跟AS子句和SELECT语句。可以在SELECT语句的任何地方使用公共表表达式定义的结果集。
WITH cte AS (
SELECT column1, column2, ...
FROM table_name
)
SELECT column1, column2, ...
FROM cte;
下面是一个例子,这个例子的目的是找到一个人在他们的小时数限制内做了多少工作:
WITH cte AS (
SELECT employees.name, SUM(hours_worked) AS hours
FROM employees
JOIN job_history
ON employees.id = job_history.employee_id
WHERE job_history.start_date BETWEEN '2021-01-01' AND '2022-01-01'
GROUP BY employees.id, employees.name
)
SELECT name, hours
FROM cte
WHERE hours <= 2000;
### 2.2 多表查询
可以在公共表表达式(CTE)中包含多个表的数据,以便在下一个SELECT语句中一次性检索它们。这可能很有用,例如,当我们需要一个参数来在几个表中查找数据集时。
WITH projects AS (
SELECT *
FROM project
WHERE project_type = 'Research'
),
sales AS (
SELECT *
FROM project
WHERE project_type = 'Sales'
)
SELECT *
FROM projects
UNION ALL
SELECT *
FROM sales;
### 2.3 递归查询
可以在WITH AS语句中创建递归查询,这是一种有趣且独特的语法,可以在CTE中引用自己。
这个例子是关于匿名帖子的论坛,我们需要遍历所有评论并获取最后的评论:
WITH RECURSIVE comments_cte AS (
SELECT comments.id, comments.parent_id, comments.content, comments.created_at
FROM comments
WHERE comments.parent_id IS NULL
UNION ALL
SELECT comments.id, comments.parent_id, comments.content, comments.created_at
FROM comments
JOIN comments_cte
ON comments.parent_id = comments_cte.id
)
SELECT *
FROM comments_cte
ORDER BY created_at DESC;
在预定的条件下,这将递归地查找所有帖子和评论的父级。
##3. 总结
MySQL中的 `WITH AS` 是一种优秀的查询技巧,在查询中使用公共表表达式(CTE)可以使查询变得更清晰、更可读。使用子查询、多表查询和递归查询等功能,可以极大地提高MySQL的查询性能和效率。