MYSQL8.0 CTE和子查询

CTE（Common Table Expression，公用表表达式）

WITH testTable AS (
  SELECT * FROM pro_leps_task WHERE task_id = 19
)
SELECT * FROM testTable;

优势

• 可读性更好：CTE 以 WITH 子句显式定义临时数据集，逻辑上更清晰，尤其是在复杂查询中（如多表关联、多层嵌套），能将子查询提取为 “临时命名表”，便于理解整体结构。
• 可复用性高：如果需要多次引用同一个子查询结果（例如在 SELECT、JOIN、WHERE 中多次使用），CTE 可以定义一次、多次引用，避免重复编写子查询，减少冗余。
• 支持递归查询：CTE 是 SQL 中实现递归查询（如层级结构数据，如部门树、评论嵌套）的标准方式，子查询无法直接支持递归。

劣势

• 对于极简单的场景（如单条子查询），写法略繁琐，不如子查询简洁。
• 部分老版本数据库（如早期 MySQL 5.7 及之前版本）对 CTE 的支持不完善（MySQL 8.0 开始完整支持），可能存在兼容性问题。

子查询（Subquery）

SELECT * FROM (
  SELECT * FROM pro_leps_task WHERE task_id = 19
) testTable;

优势

• 写法简洁：对于简单场景，子查询直接嵌套在 FROM 后，代码更紧凑，无需额外的 WITH 关键字。
• 兼容性更好：子查询是 SQL 标准的基础特性，几乎所有关系型数据库（如 MySQL、PostgreSQL、SQL Server、Oracle 等）都支持，不存在版本兼容问题。

劣势

• 可读性差：在复杂查询中（如多层嵌套子查询），代码会变得冗长且难以维护，嵌套层级越深，逻辑越难梳理。
• 复用性低：如果需要多次引用同一子查询结果，必须重复编写，增加代码冗余，且可能影响执行效率（数据库可能重复计算）。
• 不支持递归：无法用于递归场景，必须依赖 CTE 或其他特殊语法。