With As 用法

  1. 含义:WITH AS 短语,也叫做子查询部分(subquery factoring)也称公用表表达式(CTE),

  2. ,可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,作为提供数据的部分。

    对于UNION ALL,使用WITH AS定义了一个UNION ALL语句,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

  3. 本质:其实有点类似于一个容器,将查询的数据保存在容器表中,供全局使用,性能更好,写法如下:

    cr 就相当于一个表
    with
    cr as
    (
    select CountryRegionCode from person.CountryRegion where Name like 'C%'
    )

    select * from person.StateProvince where CountryRegionCode in (select * from cr)
    
    
  4. 注意点

  5. CTE 后面必须直接跟使用CTE 的SQL 语句(如select、insert、update等),否则,CTE 将失效。如下面的SQL语句将无法正常使用CTE

    with
    cr as
    (
    select CountryRegionCode from person.CountryRegion where Name like 'C%'
    )
    select * from person.CountryRegion -- 应将这条SQL语句去掉
    -- 使用CTE的SQL语句应紧跟在相关的CTE后面 --
    select * from person.StateProvince where CountryRegionCode in (select * from cr)
     
  6. 如果CTE 的表达式名称与某个数据表或视图重名,则紧跟在该CTE 后面的SQL 语句使用的仍然是CTE,当然,后面的SQL 语句使用的就是数据表或视图了,如下面的SQL 语句所示:

    --  table1是一个实际存在的表

    with
    table1 as
    (
    select * from persons where age < 30
    )
    select * from table1 -- 使用了名为table1的公共表表达式
    select * from table1 -- 使用了名为table1的数据表
     
  7. CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示

    with
    cte1 as
    (
    select * from table1 where name like 'abc%'
    ),
    cte2 as
    (
    select * from table2 where id > 20
    ),
    cte3 as
    (
    select * from table3 where price < 100
    )
    select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
     

8.注意有的时候会出一些很奇葩的问题,如果写的都对但依然报错,可以在 With 前面加; 有可能会好(只要是批处理的操作,都要在With 前面加;)

; WITH tableOne AS 
 

With as 参考资料

上一篇:我的第一个python web开发框架(13)——工具函数包说明(四)


下一篇:c++ vector(向量)使用方法详解(顺序访问vector的多种方式)