MySQL WITH AS语法的使用

在决策我们的数据路线时，MySQL的语法 WITH AS就像是在森林中绘制的地图，可以清晰明白的帮助我们规划查询路径，清点数据的"点点滴滴"。其中，WITH AS语句在SQL中也被称为公共表表达式（Common Table Expression，简称CTE）。CTE为我们在编写长、复杂的SQL查询语句时，提供了一种有效的组织方式。

来想象一下，当你在旅途的路上，拿着一面镜子。在镜子中，你能看到现在的自己，也可以看到路过的风景。在这个比喻中，可你就是CTE，是你的SQL语句；镜子就是WITH AS子句，能让你看到数据的一个临时镜像。
那么，如何使用 WITH AS语句呢？

以一条简单的查询为案例，比如你想找到所有年龄超过30岁的员工。

不使用CTE，我们的查询语句是这样：

SELECT * FROM employees
WHERE age > 30;

 

而使用了CTE后，查询语句变为：

WITH over_thirty AS (
   SELECT * FROM employees
   WHERE age > 30
)
SELECT * FROM over_thirty;

 

在这个例子中，WITH AS语法让我们创建了一个叫做 over_thirty的临时表，我们可以在以后的查询中引用它。这个查询结果和前者是一样的，但增加了可读性和复用性。比如我们不仅可以查询年龄超过30岁的员工，还想查询这部分员工中，部门为“销售”的员工，我们只需要添加一点语句：

WITH over_thirty AS (
   SELECT * FROM employees
   WHERE age > 30
)
SELECT * FROM over_thirty
WHERE department = 'sales';

 

除了增加查询语句的可读性和复用性，WITH AS语法还可以使用在递归查询上。比如查询一个员工的所有下属，直至最后一个下属。这个时候，普通SQL查询可能面临复杂和困难，但 WITH AS却能轻松处理。以下面的查询语句为例：

WITH RECURSIVE employee_hierachy AS (
   SELECT employee_id, boss_id FROM employees
   WHERE boss_id IS NULL
   UNION ALL
   SELECT e.employee_id, e.boss_id 
   FROM employee_hierachy eh JOIN employees e 
   ON eh.employee_id = e.boss_id
)
SELECT * FROM employee_hierachy;
​

在这个例子中，WITH RECURSIVE指出我们在进行递归查询，employee_hierachy是临时表的名字，定义了所有员工和他们对应的上级。UNION ALL用来合并来自递归调用的结果，实现了连续的查询搜索。