oracle的cte,sql – 在Oracle中创建CTE

您可以通过从dual中选择日期值来创建公用表表达式(CTE,

subquery factoring等),并将它们组合在一起：

with RTG_YEARS (YR) as (

select to_date('2013-01-01', 'yyyy-mm-dd') from dual

union all select to_date('2013-12-31', 'yyyy-mm-dd') from dual

union all select to_date('2014-01-01', 'yyyy-mm-dd') from dual

union all select to_date('2014-12-31', 'yyyy-mm-dd') from dual

union all select to_date('2015-01-01', 'yyyy-mm-dd') from dual

union all select to_date('2015-12-31', 'yyyy-mm-dd') from dual

)

select * from RTG_YEARS;

YR

----------

2013-01-01

2013-12-31

2014-01-01

2014-12-31

2015-01-01

2015-12-31

与CTE无关,但您可以使用date literals减少输入：

with RTG_YEARS (YR) as (

select date '2013-01-01' from dual

union all select date '2013-12-31' from dual

union all select date '2014-01-01' from dual

union all select date '2014-12-31' from dual

union all select date '2015-01-01' from dual

union all select date '2015-12-31' from dual

)

select * from RTG_YEARS;