在Oracle中创建CTE

Question

我正在尝试在Oracle中创建一个CTE，它不从现有表中进行选择，而是将数据插入其中。目前，我正在创建一个表，然后在query完成后将其删除。有没有一种方法可以创建一个有效地做同样事情的CTE？这是我当前的代码：

create table RTG_YEARS
(YR date);
insert into RTG_YEARS values (to_date('2013-01-01', 'yyyy-mm-dd'));
insert into RTG_YEARS values (to_date('2013-12-31', 'yyyy-mm-dd'));
insert into RTG_YEARS values (to_date('2014-01-01', 'yyyy-mm-dd'));
insert into RTG_YEARS values (to_date('2014-12-31', 'yyyy-mm-dd'));
insert into RTG_YEARS values (to_date('2015-01-01', 'yyyy-mm-dd'));
insert into RTG_YEARS values (to_date('2015-12-31', 'yyyy-mm-dd'));

Answer 1

您可以通过从dual中选择日期值并将它们统一在一起来创建通用表表达式(CTE、子查询因子分解等(：

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无关，但您可以通过使用日期文字来减少键入：

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;