with as 使用

背景

当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级、多个地方存在重复使用的情况，这个时候我们可以使用 with xxx as 语句 ，将其独立出来，极大提高SQL可读性，简化SQL。

注：目前 oracle、sql server、hive等均支持 with as 用法，但 mysql并不支持！

 

2019-05-31更新：MySQL8.0大量更新优化，支持Common table expressions，即支持 with 语法！

 

一、介绍

 

with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的（只在本条sql内部起作用），而且必须要和其他sql(比如select insert)一起使用才可以！

 

好处

适当的提高代码可读性

如果with子句在后面要多次使用到，这可以大大的简化SQL

更重要的是：一次分析，多次使用，这也是为什么会提供性能的地方，达到了“少读”的目标(这张临时表的sql只执行一次)。

二、使用

 

WITH 
t1 AS 
    (
        SELECT 
        *
        FROM 
        carinfo
    ), 
    t2 AS 
    (
            SELECT 
        *
            FROM 
        car_blacklist
        )
    
SELECT 
    *
FROM t1, t2

注意：这里必须要整体作为一条sql查询，即with as语句后不能加分号，不然会报错。

三、注意事项

1. with子句必须在引用它的select语句之前定义，多个子句之间只能用逗号分割，最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割，with 子句的查询必须用括号括起来。
2. 如果定义了with子句，但其后没有跟select查询，则会报错！
3. 前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句！