1、CONNECT BY:层次查询子句,一般用于树状或者层次结果集的查询
创建连续的月份
SELECT
TO_CHAR(ADD_MONTHS(TO_DATE('2019' || '0830', 'yyyy-mm-dd'), -LEVEL + 1), 'YYYY-MM') mon
FROM
dual
CONNECT BY
LEVEL < 36
--2年24个月
ORDER BY
mon;
2、WITH AS相当于虚拟视图:
特点:一次分析,多次使用,能达到少读的目的
语法:
--相当于建了个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)