从临时表中插入数据

Question

我有两个表格，一个是齿轮，另一个是齿轮轴。

create table sprockets(
    id NUMBER
);

INSERT into sprockets VALUES (4);
INSERT into sprockets VALUES (8);
INSERT into sprockets VALUES (15);
INSERT into sprockets VALUES (16);
INSERT into sprockets VALUES (23);
INSERT into sprockets VALUES (42);

create table cogs(
    id NUMBER
);

我想从sprockets中取出一些id，并将它们放入cogs中。

insert into cogs select id from sprockets s where s.id < 40 and MOD(s.id, 3) != 0;

这将按预期向 cogs 添加齿轮 4、8、16、23。

4 rows inserted

随着我的链轮制造业务的增长，决定哪些链轮需要齿轮的业务逻辑将变得更加复杂。因此，我想使用一系列临时表来过滤掉非候选链轮。我认为这比没有注释的一行语句更易于维护。

--sprockets with ids greater than 40 are too big to frob, 
--so it's impossible to weld a cog to them
with frobbableSprockets as(
    select id from sprockets where sprockets.id < 40
),

--non-greppable sprockets have built-in harmonic oscillators, 
--so cogs are not required
greppableFrobbableSprockets as(
    select id from frobbableSprockets f where MOD(f.id,3) != 0
),

--not pictured: more filtering using arcane business logic, 
--including but not limited to:
--whether it is raining on a tuesday,
--and if the moon is in the seventh house.

--sprockets with ids less than 3 are from the legacy system and already have cogs
sprocketsRequiringCogs as(
    select id from greppableFrobbableSprockets f where f.id > 3
)

insert into cogs select id from sprocketsRequiringCogs

这段代码相对易读，但不幸的是它并不能正常工作！

insert into cogs select id from sprocketsRequiringCogs 
Error at Command Line:18 Column:2
Error report:
SQL Error: ORA-00928: missing SELECT keyword

如果我将最后一行更改为select id from sprocketsRequiringCogs，则没有错误，因此我知道问题必须在插入语句中而不是临时表的声明中。

单行插入语句可以工作，而多行插入语句无法工作。 我唯一看到的区别是后者从临时表中获取其值。

为什么我不能从临时表中插入行？

- Kevin

---

请勿在查询中使用 *。 - Justin Skiles

Answer 1

这只是一个猜测，我现在没有试验的可能性，但是可能会起作用：

insert into cogs
with frobbableSprockets as(
    select * from sprockets where sprockets.id < 40
),
...
select * from sprocketsRequiringCogs

- ekholm

---

嗯，这个有效！不过我想知道为什么？也许 with 块必须紧接着一个 select 语句吧？我原以为它们可以放在任何地方... - Kevin

@Kevin - 根据此链接中的 WITH with CONNECT BY 示例（http://psoug.org/reference/with.html），似乎 insert into cogs 必须放在第一个 WITH 子句之前。 - LittleBobbyTables - Au Revoir

@Kevin：是的，我认为你说得没错，WITH只能在后面跟着SELECT使用，详情见http://www.morganslibrary.org/reference/with.html。 - ekholm

Answer 2

 insert into cogs (Id) select Id from sprocketsRequiringCogs 

- purplesoft

---

检查类型时，创建临时表时服务器会自动分配类型。SQL语句是正确的。 - purplesoft

你的意思是我必须为临时表中的每一列指定类型吗？我该怎么做呢？我查看了 WITH 的文档，但我不清楚如何给它们指定类型。 - Kevin

将其转换为 MSSQL 中的 cast(id as int) 或 convert(int, id)。 - purplesoft

Answer 3

尝试通过内联视图来替换WITH子句，即在FROM子句中使用您的SELECT语句（使用(select)）。

- Alexander Tokarev

---

如果你的意思是我可以使用嵌套的选择语句代替临时表，那么你是正确的。但是如果可能的话，我想保留我的小临时表。它们可以减少很多水平滚动。 - Kevin

是的，如果目标是为了摆脱水平滚动，那么您是正确的，但如果您想得到有效的SQL，则替换将是最佳解决方案。 - Alexander Tokarev