sql 之 with as

很少使用with as 语句，但是对其了解不多，专门学习一下。

with as 语句的作用

with as语句可以用来做什么？

with as语句部分也叫做子查询部分，定义一个SQL片断后，该SQL片断可以被整个SQL语句所用到。

有的时候，with as是为了提高SQL语句的可读性，减少嵌套冗余。

不使用with as语句，sql比较冗余，如下：

使用with as语句，精简了不少sql，如下：

先执行select * from ods.repay_schedule WHERE product_id ='003201' and due_bill_no='11a186fa3b62fec2d296d96f32546076'and '2022-02-28' between s_d_date and date_sub(e_d_date,1) 把结果放到一个临时表temp_table中，作为全局使用。

with as的用法可以通俗点讲是，讲需要频繁执行的slq片段加个别名放到全局中，后面直接调用就可以，这样减少调用次数，优化执行效率。

以上的两种方式达到效果是一样。

在标准的数据库中，如hive，Oracle，DB2，SQL SERVER，PostgreSQL都是支持WITH AS 语句进行递归查询。只有MySQL是不支持的。（MySQL全部版本都不支持？）

-- 不使用with as语句
select due_bill_no,schedule_status
from
(select *
from ods.repay_schedule
WHERE product_id ='003201' and due_bill_no='11a186fa3b62fec2d296d96f32546076'
and '2022-02-28' between s_d_date and date_sub(e_d_date,1) ) as temp_table
where schedule_status ='F';

select due_bill_no,schedule_status
from
(select *
from ods.repay_schedule
WHERE product_id ='003201' and due_bill_no='11a186fa3b62fec2d296d96f32546076'
and '2022-02-28' between s_d_date and date_sub(e_d_date,1) ) as temp_table
where schedule_status ='N';

select due_bill_no,schedule_status
from
(select *
from ods.repay_schedule
WHERE product_id ='003201' and due_bill_no='11a186fa3b62fec2d296d96f32546076'
and '2022-02-28' between s_d_date and date_sub(e_d_date,1) ) as temp_table
where schedule_status ='O';

-- 使用with as语句
with temp_table as   -- 这里定义使用with as 定义temp_table
(select *
from ods.repay_schedule
WHERE product_id ='003201' and due_bill_no='11a186fa3b62fec2d296d96f32546076'
and '2022-02-28' between s_d_date and date_sub(e_d_date,1) )

select due_bill_no,schedule_status    -- 后面的查询可以重复使用temp_table，不用每次都写一大段
from temp_table
where schedule_status ='F';

select due_bill_no,schedule_status
from temp_table
where schedule_status ='N';

select due_bill_no,schedule_status
from temp_table
where schedule_status ='O';      

with as 语句的用法

With alias_name as (select1)[,alias_namen as (select n) ] --中括号可以省略

Select …

如：

with
t1 as
(
    select * from table1 
),
t2 as
(
    select * from table2
),
t3 as
(
    select * from table3 
)
select * from t1 a, t2 b, t3 c 
-- 注：最后一个as不加逗号。      

with as 语句的相关总结

1.使用with子句可以让子查询重用相同的with查询块，通过select调用(with子句只能被select查询块引用)，一般在with查询用到多次情况下。在引用的select语句之前定义，同级只能定义with关键字只能使用一次，多个用逗号分割。

2.with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用，提高效率。

3.在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。

4.最后一个with子句与下面的查询之间不能有逗号，只通过右括号分割，with子句的查询必须用括号括起来。

5.如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。(至少一个with查询的name未被引用，解决方法是移除未被引用的with查询)，注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。

6.前面的with子句定义的查询在后面的with子句中可以使用，但是一个with子句内部不能嵌套with子句。

7.当一个查询块名字和一个表名或其他的对象相同时，解析器从内向外搜索，优先使用子查询块名字。

8.with查询的结果列有别名，引用的时候必须使用别名或*。