第一次看到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 e2WHERE e2.department_id = e1.department_id
);-- 使用CTE
WITH MaxSalaries AS (SELECT department_id, MAX(salary) AS max_salaryFROM employeesGROUP 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显得更清晰，因为这将最大工资的计算与主查询分离开来，使得整个查询结构更易于理解和维护。