SQL 在 Oracle 中创建 CTE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38775056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a CTE in Oracle
提问by mosk915
I am trying to create a CTEin Oraclethat 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 queryis done. Is there a way to create a CTEthat effectively does the same thing? This is my current code:
我正在尝试创建一个不从现有表中选择而是将数据插入其中的CTEin Oracle。目前，我正在创建一个表，然后在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'));
回答by Alex Poole
You can create your common table expression (CTE, subquery factoring, etc.) by selecting the date values from dual, and unioning them all together:
您可以通过从 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
Not related to it being a CTE, but you can reduce the typing a bit by using date literals:
与它是 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;
