SQL中的子查询和CTE（with ....as..）

第一次看到with as 这种类似于python中读文件的写法还是挺疑惑的，其实它是CTE，功能和子查询很类似但又有不同点，在实际应用场景中具有着独特作用。

子查询

子查询是在主查询中的嵌套查询，可以出现在SELECT、FROM、WHERE等子句中。子查询可以是标量子查询、行子查询或表子查询。

优点：

    •    简单的查询结构，对于小规模查询可以很方便地使用。
    •    适用于一次性使用的临时计算。

缺点：

    •    可读性差：嵌套查询可能使SQL语句变得难以理解，特别是当嵌套层次较深时。
    •    不能复用：子查询只能在定义它的查询中使用，无法在其他地方重用。

CTE（Common Table Expressions）

CTE是在SQL语句的开头使用WITH关键字定义的临时结果集，随后可以在主查询中引用这个结果集。CTE在某些数据库系统中也被称为”公用表表达式”。
优点：

    •    可读性好：将复杂的查询分解为多个易于理解的部分。
    •    复用性强：同一个CTE可以在主查询中多次引用，提高查询的效率。
    •    递归查询：CTE支持递归查询，这是子查询无法做到的。

缺点：

    •    对于简单的查询可能显得冗余，不如子查询简洁。
    •    性能上不一定优于子查询，具体视情况而定，需要根据具体数据库和查询场景测试性能。

适用场景

子查询适用于：

    •    简单查询或一次性使用的临时计算。
    •    嵌套在WHERE、FROM或SELECT子句中时。

CTE适用于：

    •    复杂查询，将复杂查询分解为多个易于理解的部分。
    •    需要在查询中多次引用同一结果集时。
    •    递归查询，处理层次结构数据（如组织结构图、树形结构等）。

示例

假设我们有一个employees表，我们想要找出每个部门工资最高的员工：

-- 使用子查询
SELECT department_id, employee_id, salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- 使用CTE
WITH MaxSalaries AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.department_id, e.employee_id, e.salary
FROM employees e
JOIN MaxSalaries ms ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

在这个例子中，使用CTE显得更清晰，因为这将最大工资的计算与主查询分离开来，使得整个查询结构更易于理解和维护。