sql – 在Oracle中创建CTE

发布时间：2020-12-12 16:21:34 所属栏目：MsSql教程来源：网络整理

导读：我试图在Oracle中创建一个CTE,它不从现有表中进行选择,而是将数据插入其中.目前,我正在创建一个表,然后在查询完成后删除它.有没有办法创建一个有效地做同样事情的CTE？这是我目前的代码： create table RTG_YEARS(YR date);insert into RTG_YEARS values (to_

我试图在Oracle中创建一个CTE,它不从现有表中进行选择,而是将数据插入其中.目前,我正在创建一个表,然后在查询完成后删除它.有没有办法创建一个有效地做同样事情的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'));

解决方法

您可以通过从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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!