Oracle SQL插入with子句 [英] Oracle SQL insert into with With clause
本文介绍了Oracle SQL插入with子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我是sql的新手,所以也许这是一个愚蠢的问题,但是是否可以将With子句与Insert Into一起使用?还是有任何常见的解决方法?我的意思是这样的:
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:
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
推荐答案
您可以根据需要使用任意多个'helper_tables'.
You may use as many 'helper_tables' as you wish.
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
这篇关于Oracle SQL插入with子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文