我们利用Oracle 自带的emp表来做示例。
select * from scott.emp;
通过MAX与MIN获取DEPTNO列中最大值30与最小值10,利用层级函数connect 一个一个补齐10到30之间所有数字
select * from
(select beginnumber + (level - 1) as numbers
from (select min(deptno) as beginnumber,
max(deptno) as endnumber
from scott.emp)
connect by level <= endnumber - beginnumber + 1)
通过MAX与MIN获取HIREDATE列中最大值1987/5/23与最小值1980/12/17,并通过extract函数取出年份值,利用层级函数connect一年一年补齐1980到1987之间所有年。
select * from
(select beginyear + (level - 1) as years
from (select extract(year from min(hiredate)) as beginyear,
extract(year from max(hiredate)) as endyear
from scott.emp)
connect by level <= endyear - beginyear + 1)
关键语句
connect by 层次查询 与 level 关键字 层级编号
extract()截取函数
max()获取最大值函数
min()获取最小值函数