1. CTE:和派生表非常类似,也相当于临时表,但定义后必须在后面的sql语句中使用。
2. 语法:
WITH CTE_Name AS(<Inner_Query_Of_CTE>)<Out_Query_Of_CTE>
3. 特点:
4.实例:(1): 一次定义多次引用。(2): 多个cte定义之前用逗号(,)分开,不加with,用表名和as即可。(3): 可以减少派生表的数量,减少代码的冗余度。使代码更简洁。
(1)创建实验表:
SQL> create table contry as select * from countries; Table created.
(2)试验代码:
注:aa是第一个cte表,bb是第二个cte表,cc表使表aa和表bb以ID做关联得到。with aa as ( select country_id ,row_number() over(order by country_id ) as ida from contry a ) , bb as ( select country_id ,row_number() over(order by country_id ) as idb from contry ) , cc as ( select aa.ida, bb.idb from aa left join bb on aa.ida=bb.idb ) select * from cc;