带有插入语句的火鸟 CTE 结果数据集

Question

我在以下代码中确实遇到了困难，我找不到在该代码中放置插入语句的位置，它将插入来自 CTE 的返回值。

insert into ntm (num,comb,0,0,0,0,0,0,0,0,0,0,0,0)
with CTE as (
  SELECT a.ELM_NUM as num, 
         a.N_1 as N1,
         (trim('1.4*'||a.COMBO)||' + ' || '1.6*'||b.COMBO) as com2,
         b.ELM_NUM as num2 
    FROM NTM a cross join ntm b 
    where ((a.Combo='dead' and b.combo='live') or (a.Combo='dead' and b.combo='live')) and a.ELM_NUM=b.ELM_NUM) 
  select num, num2, trim(trim(com2)||' + '||trim(d.combo)), N1, 1.4*N1
    from CTE cross join ntm d
    where (d.elm_num = cte.num) and (position('X',combo)>0 or position('Y',combo)>0 ) 

火焰罗宾引擎引发的错误"令牌未知 - 第 1 行，第 27 列0

CREATE TABLE NTM
(
ELM_NUM Integer NOT NULL,
COMBO Char(40) NOT NULL,
N_1 Double precision,
N_2 Double precision,
V2_1 Double precision,
V2_2 Double precision,
V3_1 Double precision,
V3_2 Double precision,
M2_1 Double precision,
M2_2 Double precision,
M3_1 Double precision,
M3_2 Double precision,
MT_1 Double precision,
MT_2 Double precision
);

Answer 1

您可以通过在 select

语句（包括 WITH ）之前添加 INSERT INTO <table> (<column>[, <column>]*) 来插入 select 语句的结果。因此，如果您有疑问：

WITH a AS (
    SELECT 4 AS ID, 'Item 4' AS NAME FROM RDB$DATABASE
) 
SELECT ID, NAME 
FROM a

并且您想插入到具有列ID和NAME列的表格ITEMS中：

INSERT INTO ITEMS (ID, NAME) 
WITH a AS (
    SELECT 4 AS ID, 'Item 4' AS NAME FROM RDB$DATABASE
) 
SELECT ID, NAME 
FROM a

这适用于Firebird 2.5，但我认为它也适用于Firebird 2.1。

根据您的编辑：

您的语法完全错误：insert into ntm (num,comb,0,0,0,0,0,0,0,0,0,0,0,0)无效：您在表 ntm 中没有列num和comb，并且将0指定为列名是非法的。您似乎将INSERT列列表与值列表混淆了。

INSERT的语法为：

INSERT INTO <object> [(col [, col …])]
{VALUES (<val> [, <val> …]) | <select_expr>}

您尝试使用类似以下内容：

INSERT INTO <object> (<val> [, <val> …])
<select_expr>

假设您只想从所选内容中插入num（ELM_NUM）和trim(trim(com2)||' + '||trim(d.combo))（插入COMBO）列，则应使用：

insert into ntm (ELM_NUM,COMBO)
with CTE as (
    SELECT a.ELM_NUM as num, 
         a.N_1 as N1,
         trim('1.4*'||a.COMBO)||' + ' || '1.6*'||b.COMBO as com2,
         b.ELM_NUM as num2 
    FROM NTM a cross join ntm b 
    where (a.Combo='dead' and b.combo='live' or a.Combo='dead' and b.combo='live') and a.ELM_NUM=b.ELM_NUM
) 
select num, trim(trim(com2)||' + '||trim(d.combo))
from CTE cross join ntm d
where d.elm_num = cte.num and (position('X',combo) > 0 or position('Y',combo) > 0)

请注意，我从选择中删除了其他列，因为您没有插入这些列（选择列表中的列数必须与插入列列表中的列数匹配）。

如果希望其余列为 0，则需要向表定义添加一个DEFAULT 0，或者需要列出所有列并在选择列表中为每个附加列添加一个0值。