1.语法格式
{CONNECT BY [NOCYCLE] condition [AND condition]...[START WITH condition]
|START WITH condition CONNECT BY [NOCYCLE] condition [AND condition]...}
2.关于 connect by level的小例子
请看下面的Sql:
select * from (
select
level ,
to_char(add_months(date'2017-01-1',level-1),'yyyymmdd') as start_date,
to_char(add_months(add_months(date'2017-01-1',level-1),3),'yyyymmdd') as end_date_3m,
to_char(add_months(add_months(date'2017-01-1',level-1),6),'yyyymmdd') as end_date_6m,
to_char(add_months(add_months(date'2017-01-1',level-1),12),'yyyymmdd') as end_date_12m
from dual
connect by level <=100
)
order by start_date asc;
解释:
上面的Sql主要作用就是生成(树状)一个维表,方便使用。 to_char(add_months(add_months(date'2017-01-1',level-1),3),'yyyymmdd') as end_date_3m,其实就是 对这个date‘2017-01-1’ 做了两次的处理,2017-01-1+ 加上一个月的间隔+加上三个月的间隔,组成一个列,以此类推……