Oracle SQL insert into with with 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5885154/
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
Oracle SQL insert into with With clause
提问by user2424380
I'm new to sql, so maybe it is a dumb question, but is there any possibility to use With clause with Insert Into? Or are there any common workarounds? I mean something like this:
我是 sql 的新手，所以这可能是一个愚蠢的问题，但是有没有可能将 With 子句与 Insert Into 一起使用？或者有什么常见的解决方法？我的意思是这样的：
With helper_table As (
Select * From dummy2
)
Insert Into dummy1 Values (Select t.a From helper_table t Where t.a = 'X' );
Thx!
谢谢！
My example is too dummy, so I add some extended code (thx for the answers so far).
我的例子太假了，所以我添加了一些扩展代码（感谢到目前为止的答案）。
INSERT
INTO dummy values (a,b) //more values
WITH helper_table AS
(
SELECT *
FROM dummy2
)
WITH helper_table2 AS //from more tables
(
SELECT *
FROM dummy3
)
SELECT t.value as a, t2.value as b
FROM helper_table t
join helper_table t2 on t.value = t2.value //some join
WHERE t.value = 'X' and t2.value = 'X' //other stuff
回答by Harrison
You may use as many 'helper_tables' as you wish.
您可以根据需要使用任意数量的“helper_tables”。
create table t(helper1 varchar2(50) , helper2 varchar2(50) , dataElement varchar2(50) );
insert into t(helper1, helper2, dataelement)
with
de as(select level lvl from dual connect by level <10)
,h1 as (select lvl, lvl/1.5 hp from de)
,h2 as (select lvl, lvl/2 hp2 from de)
select h1.hp , h2.hp2, de.lvl
from de
inner join
h1 on de.lvl = h1.lvl
inner join
h2 on de.lvl = h2.lvl
/
With this in mind, you may be able to do all of your joins via normal joining of the tables to the master table
考虑到这一点，您可以通过将表正常连接到主表来完成所有连接
回答by Quassnoi
INSERT
INTO dummy1
WITH helper_table AS
(
SELECT *
FROM dummy2
)
SELECT t.a
FROM helper_table t
WHERE t.a = 'X'
回答by Justin Cave
You can do something like
你可以做类似的事情
INSERT INTO dummy1
WITH helper_table AS (
SELECT *
FROM dummy2
)
SELECT t.a
FROM helper_table t
WHERE t.a = 'X';
For your updated query
对于您更新的查询
INSERT
INTO dummy values (a,b) //more values
WITH helper_table AS
(
SELECT *
FROM dummy2
),
helper_table2 AS //from more tables
(
SELECT *
FROM dummy3
)
SELECT t.value as a, t2.value as b
FROM helper_table t
join helper_table t2 on t.value = t2.value //some join
WHERE t.value = 'X' and t2.value = 'X' //other stuff
回答by user3807115
Keep in mind, using CTE's is intended for a small amount of data. Having thousands of rows in CTE's may cause performance degradation.
请记住，使用 CTE 的目的是处理少量数据。CTE 中有数千行可能会导致性能下降。
This is because all the helper table content is stored in PGA if not in the TEMP
这是因为如果不在 TEMP 中，所有辅助表内容都存储在 PGA 中
