在 Oracle 中创建 CTE

Creating a CTE in Oracle(在 Oracle 中创建 CTE)

问题描述

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

I am trying to create a CTE in Oracle that doesn't select from an existing table but instead has data inserted into it. Currently, I am creating a table and then dropping it after the query is done. Is there a way to create a CTE that effectively does the same thing? This is my current code:

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'));

推荐答案

您可以创建公用表表达式(CTE，子查询分解等)通过从对偶中选择日期值并将它们合并在一起:

You can create your common table expression (CTE, subquery factoring, etc.) by selecting the date values from dual, and unioning them all together:

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 无关，但您可以使用 日期文字:

Not related to it being a CTE, but you can reduce the typing a bit by using 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;

这篇关于在 Oracle 中创建 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！