一. CONNECT BY LEVEL函数
connect by level 是oracle中一组关键字,是用来实现递归查询的,譬如说实现查询 1,2,3,4 …n 的数字可以使用connect by level
- 查询1到n
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10
2. 查询最近五年
SELECT TO_CHAR(SYSDATE, 'YYYY') - LEVEL + 1 YEARS FROM DUAL CONNECT BY LEVEL <= 5
二. CONNECT BY ROWNUM函数
大部分情况下同connect by level函数,但内部执行原理及过程不同(有兴趣可以自行研究)
- 查询1-10
select level,rownum from dual connect by level<=10;
select level,rownum from dual connect by rownum<=10;
2. 查询一年的1-12月份
SELECT '2016' || SUBSTR('0' || TO_CHAR(ROWNUM), -2, 2) YYMM FROM DUAL CONNECT BY ROWNUM <= 12