MySQL CTE|极客笔记

MySQL CTE

什么是CTE

CTE，全称为Common Table Expressions，是一种用于创建临时结果集的SQL查询的方法。CTE可以帮助我们简化复杂查询、提高查询性能，以及增强查询的可读性。

CTE的语法

在MySQL中，使用CTE需要使用WITH关键字来定义，其语法如下：

WITH cte_name AS (
    -- CTE查询语句
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)

CTE的优点

提高可读性

CTE可以让我们在查询中定义临时结果集，可以提高查询的可读性。通过给CTE取一个可描述性的名称，可以使代码更易于理解和维护。

简化复杂查询

在处理复杂的查询时，CTE可以帮助我们简化查询的结构。通过将查询分解成多个部分，每个部分都可以使用独立的CTE，然后将这些部分组合在一起，可以使查询更易于理解和调试。

提高查询性能

使用CTE可以避免多次重复执行相同的子查询，从而提高查询性能。由于CTE可以被重复引用，数据库系统可以将CTE查询结果存储在内存中，减少IO操作，提升查询速度。

示例

假设我们有一个数据库中包含员工表employees和部门表departments，现在我们需要查询每个部门的平均工资，并且只显示平均工资高于公司平均工资的部门。我们可以使用CTE来实现这个查询：

WITH department_avg_salary AS (
    SELECT 
        d.department_id,
        d.department_name,
        AVG(e.salary) AS avg_salary
    FROM 
        departments d
    JOIN employees e ON d.department_id = e.department_id
    GROUP BY 
        d.department_id, d.department_name
),
company_avg_salary AS (
    SELECT 
        AVG(salary) AS avg_salary
    FROM 
        employees
)
SELECT 
    d.department_name,
    d.avg_salary
FROM 
    department_avg_salary d
CROSS JOIN 
    company_avg_salary c
WHERE 
    d.avg_salary > c.avg_salary;

在这个示例中，我们首先定义了两个CTE，department_avg_salary用来计算每个部门的平均工资，company_avg_salary用来计算公司整体的平均工资。然后我们将这两个CTE和原始表departments进行关联，并通过比较部门的平均工资和公司整体平均工资来筛选出符合条件的部门。