WITH AS,也叫子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,作为提供数据的部分。
对于UNION ALL,使用WITH AS定义了一个UNION ALL语句,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。
/*with as的简单使用*/ with table1(id,mc,time) as (select top 7 egpuser_id,egpuser_mc,egp_modify_time from T_EGP_USER order by egp_modify_time asc) select * from table1;/*with as 后面的sqsl必须使用table1,否则报错*/
WITH AS的优点:
(1). SQL可读性增强。比如对于特定with子查询取个有意义的名字等。
(2)、with子查询只执行一次,将结果存储在用户临时表空间中,可以引用多次,增强性能。
with egpDepCte(levelNo, egpdep_id) as /*with as:公用表表达式,可以理解为创建临时表,仅供后面的sql语句使用。*/ ( select 0 levelNo, egpdep_id from t_egp_dep where egpdep_lx = '1' and egpdep_id = '3' union all/*Union All:对两个结果集进行并集操作,包括重复行,不进行排序;*/ select (a.levelNo+1) levelNo, b.egpdep_id from egpDepCte a, t_egp_dep b where a.egpdep_id = b.egpdep_pid and b.egpdep_lx = '1' and (a.levelNo+1) < 4 ) select a.* from t_egp_dep a, egpDepCte b where a.egpdep_pid = b.egpdep_id and a.egpdep_lx = '0' and a.egpdep_relation_id in (52,58,154) order by b.levelNo, a.egp_order