MySQL 通用表表达式（CTE）的用法 子查询的高效使用

原文地址​​​​​​​

MySQL的CTE有两种，一种是非递归的方式，另一种是递归的方式。

我们为什么需要使用CTE？

在同一个查询中不可能两次引用派生表。因此，派生表查询会计算两次或两次以上，这表明存在严重的性能问题。使用CTE，子查询只计算一次。

CTE 非递归方式

我们通常对派生表的使用是这样子的：

SELECT... FROM (subquery) AS derived, t1 ...

subquery这个子查询是放在FROM子句中。

CTE的语法如下所示：

SELECT... WITH derived AS (subquery) SELECT ... FROM derived, t1 ...  

这个subquery子查询是放在WITH AS子句中的，放在SELECT/UPDATE/DELETE，包括WITH derived AS 之句之前。

假如你要找出每年的薪资同比上一年上涨的百分比，如果不使用CTE，你需要两个子查询，并且这两个子查询相同，MySQL并不能识别出这两个是相同的查询，从而导致查询两次：

mysql> SELECT
q1.year,
q2.year AS next_year,
q1.sum,
q2.sum AS next_sum,
100*(q2.sum-q1.sum)/q1.sum AS pct
FROM
(SELECT year(from_date) as year, sum(salary) as
sum FROM salaries GROUP BY year) AS q