with as 定义 with A as (select * from class) 也就是将重复用到的大批量 的SQL语句,放到with as 中,加一个别名,在后面用到的时候就可以直接用。对于大批量的SQL数据,起到优化的作用。 注意点: 1、with子句只能被select查询块引用 2.with子句的返回结果存到用户的临时表空间中,只做一次查询,反复使用,提高效率。 3.在同级select前有多个查询定义的时候,第1个用with,后面的不用with,并且用逗号隔开。 4.最后一个with 子句与下面的查询之间不能有逗号,只通过右括号分割,with 子句的查询必须用括号括起来 用法: –针对一个别名 with tmp as (select * from tb_name) –针对多个别名 with tmp as (select * from tb_name), tmp2 as (select * from tb_name2), tmp3 as (select * from tb_name3), … –相当于建了个e临时表 with e as (select * from scott.emp e where e.empno=7499) select * from e; –相当于建了e、d临时表 with e as (select * from scott.emp), d as (select * from scott.dept) select * from e, d where e.deptno = d.deptno; |
|手机版|小黑屋|梦想之都-俊月星空 ( 粤ICP备18056059号 )
GMT+8, 2024-9-20 02:31 , Processed in 0.020885 second(s), 17 queries .
Powered by Discuz! X3.5
© 2001-2024 Discuz! Team.