sql with as 用法_oracle pg库 with .. as ..用法的妙处

日常解决问题中会用到with ... as

当在sql查询时需要对有规律的一批数据进行分析处理而又不想将这批数据存入实体表时，我们可以使用with关键字临时构建一个虚拟的数据集，以便对其进行与实体表相似的sql操作，with关键字构造的虚拟数据集临时存放于用户的临时表空间中，如：

oracle：

with hi as (

select 20 as ID,30 as dd,40 as cc from dual

union

select 50 ,60,70 from dual

union

select 80,90,100 from dual )

select * from hi where cc<100;

pg库：

with hi as (

select 20 as id,30 as dd,40 as cc

union

select 50 ,60,70

union

select 80,90,100 )

select * from hi where cc<100;