insert语句报错

这段sql长这样

insert into t_track_pro_code (track_pro_code,track_pro_code_name) select (with tb1 as (
select distinct m.track_pro_code from ba_bgt_info_hz m where m.ori_bgt_id in (
      select t.bgt_id from ba_bgt_info_hz t where t.track_pro_code in (select distinct track_pro_code from ba_bgt_info_hz 
where bgt_doc_title like '%车辆购置税收入补助地方资金%' and is_deleted=2 and track_pro_code is not null and importtype not in (8,9)) 
and t.billstatus>=0 and t.importtype not in (8,9) 
    )  and m.importtype not in (8,9)  union  
    select distinct track_pro_code from ba_bgt_info_hz 
where bgt_doc_title like '%车辆购置税收入补助地方资金%' and is_deleted=2 and track_pro_code is not null and importtype not in (8,9)
) select distinct track_pro_code track_pro_code_name from tb1) track_pro_code,'车辆购置税收入补助地方资金' 
track_pro_code_name from dual

大概就是这样写的tab1只有两列哈。
insert into tab1 select (一个临时表)，'名称' from dual；
这样报错，因为我临时表查出来是两行值 不重复的，然后name列我想写死。我想达到的效果是
1234 资金a
9876 资金a
//其中1234和9876是我从with表查出来的
有没有宝宝帮我改一下代码 达到我想要的效果！有偿！

INSERT INTO t_track_pro_code (track_pro_code, track_pro_code_name) 
WITH tb1 AS (
SELECT DISTINCT m.track_pro_code, '车辆购置税收入补助地方资金' track_pro_code_name
FROM ba_bgt_info_hz m
WHERE m.ori_bgt_id IN (
      SELECT t.bgt_id
FROM ba_bgt_info_hz t
WHERE t.track_pro_code IN (SELECT DISTINCT track_pro_code
FROM ba_bgt_info_hz
WHERE bgt_doc_title LIKE '%车辆购置税收入补助地方资金%'
AND is_deleted = 2
AND track_pro_code IS NOT NULL
AND importtype NOT IN (8, 9))
    AND t.billstatus >= 0
    AND t.importtype NOT IN (8, 9) 
    )
AND m.importtype NOT IN (8, 9)
UNION  
    SELECT DISTINCT track_pro_code, '车辆购置税收入补助地方资金' track_pro_code_name
FROM ba_bgt_info_hz
WHERE bgt_doc_title LIKE '%车辆购置税收入补助地方资金%'
AND is_deleted = 2
AND track_pro_code IS NOT NULL
AND importtype NOT IN (8, 9)
) SELECT DISTINCT track_pro_code ,track_pro_code_name
FROM tb1

临时表几个字段？这样试试？只要字段类型跟个数能对上应该没啥大问题
insert into tab1 select 临时表的字段,'名称' from 临时表;

这样试试

insert into t_track_pro_code
  (track_pro_code, track_pro_code_name)
  select distinct m.track_pro_code, '车辆购置税收入补助地方资金'
    from ba_bgt_info_hz m
   where m.ori_bgt_id in
         (select t.bgt_id
            from ba_bgt_info_hz t
           where t.track_pro_code in
                 (select distinct track_pro_code
                    from ba_bgt_info_hz
                   where bgt_doc_title like '%车辆购置税收入补助地方资金%'
                     and is_deleted = 2
                     and track_pro_code is not null
                     and importtype not in (8, 9))
             and t.billstatus >= 0
             and t.importtype not in (8, 9))
     and m.importtype not in (8, 9)
  union
  select distinct track_pro_code, '车辆购置税收入补助地方资金'
    from ba_bgt_info_hz
   where bgt_doc_title like '%车辆购置税收入补助地方资金%'
     and is_deleted = 2
     and track_pro_code is not null
     and importtype not in (8, 9)

WITH表字段看看

你至少应该把涉及到的表结构贴出来吧，问题描述太不清晰。

tab1 和临时表的字段个数以及对应的类型要一致

insert into 需要临时表是查询语句的表字段对应上，并且类型一致就行了

with tb1 as (
select distinct m.track_pro_code 
from ba_bgt_info_hz m 
where m.ori_bgt_id in (
      select t.bgt_id 
      from ba_bgt_info_hz t 
      where t.track_pro_code in (
      select distinct track_pro_code 
      from ba_bgt_info_hz 
where bgt_doc_title like '%车辆购置税收入补助地方资金%' 
and is_deleted=2 
and track_pro_code is not null 
and importtype not in (8,9)) 
and t.billstatus>=0 
and t.importtype not in (8,9) 
    )  
and m.importtype not in (8,9)  

union  

select distinct track_pro_code 
from ba_bgt_info_hz 
where bgt_doc_title like '%车辆购置税收入补助地方资金%' 
and is_deleted=2 
and track_pro_code is not null 
and importtype not in (8,9)
) 
insert into t_track_pro_code (track_pro_code,track_pro_code_name)
select distinct track_pro_code,'车辆购置税收入补助地方资金' track_pro_code_name 
from tb1;

子查询中的条件可能不完整

表结构还有基础数据给出来啊

1、题主的SQL产生的Oracle报错应该是如下图所示的“ORA-01427: single-row subquery returns more than one row”，是你的SQL写错了！

代码

create table t1(code number(10));

insert into t1(code) select level from dual connect by level < 11;

select distinct code name from t1 order by code;

select (select distinct code name from t1) code from dual; /*这里模拟你的SQL写法，其实也有问题，应该是题主多打了一个name */

证明代码示例：

drop table t_track_pro_code;
drop table ba_bgt_info_hz;

create table t_track_pro_code(track_pro_code varchar2(30),track_pro_code_name varchar2(60));
create table ba_bgt_info_hz(track_pro_code varchar2(30),bgt_id number(16),ori_bgt_id number(16),bgt_doc_title varchar2(100),is_deleted number(1),importtype number(2),billstatus number(2));

insert into ba_bgt_info_hz values('aaa',101,1,'车辆购置税收入补助地方资金',2,1,1);
insert into ba_bgt_info_hz values('aaa',101,1,'title',2,1,1);
insert into ba_bgt_info_hz values('aaa',101,1,'车辆购置税收入补助地方资金',0,1,1);
insert into ba_bgt_info_hz values('aaa',101,1,'车辆购置税收入补助地方资金',2,8,1);
insert into ba_bgt_info_hz values('aaa',101,1,'车辆购置税收入补助地方资金',2,8,-1);
insert into ba_bgt_info_hz values('bbb',102,2,'title',2,1,1);
insert into ba_bgt_info_hz values('ccc',103,3,'车辆购置税收入补助地方资金',2,1,1);

select * from ba_bgt_info_hz;

insert into t_track_pro_code (track_pro_code,track_pro_code_name) select (with tb1 as (
select distinct m.track_pro_code from ba_bgt_info_hz m where m.ori_bgt_id in (
      select t.bgt_id from ba_bgt_info_hz t where t.track_pro_code in (select distinct track_pro_code from ba_bgt_info_hz 
where bgt_doc_title like '%车辆购置税收入补助地方资金%' and is_deleted=2 and track_pro_code is not null and importtype not in (8,9)) 
and t.billstatus>=0 and t.importtype not in (8,9) 
    )  and m.importtype not in (8,9)  union  
    select distinct track_pro_code from ba_bgt_info_hz 
where bgt_doc_title like '%车辆购置税收入补助地方资金%' and is_deleted=2 and track_pro_code is not null and importtype not in (8,9)
) select distinct track_pro_code track_pro_code_name from tb1) track_pro_code,'车辆购置税收入补助地方资金' 
track_pro_code_name from dual;

报错证明与我的猜测一致！

对应于你的SQL，错误在这里：

针对该SQL的改进，我的建议有很多，详见下面。

2、最简单但却非最优的改法。如果你写这个SQL的目的是为了保留业务逻辑（因为你这个SQL写的属实有点烂，我也做过Oracle上的ETL，有些业务要求的确是要这样的），可以直接在子查询内部构建那个字符串字段：

insert into t_track_pro_code (track_pro_code,track_pro_code_name) with temp as (
select distinct track_pro_code from ba_bgt_info_hz 
where bgt_doc_title like '%车辆购置税收入补助地方资金%' and is_deleted=2 and track_pro_code is not null and importtype not in (8,9)),
tb1 as (
select distinct m.track_pro_code from ba_bgt_info_hz m where m.ori_bgt_id in (
      select t.bgt_id from ba_bgt_info_hz t where t.track_pro_code in (select track_pro_code from temp) and t.billstatus>=0 and t.importtype not in (8,9) 
)  and m.importtype not in (8,9)
    union select track_pro_code from temp     /*因前面已经去重了，union也会去重，所以这里无需distinct*/
)
select track_pro_code,'车辆购置税收入补助地方资金' track_pro_code_name from tb1;

还应注意track_pro_code,track_pro_code_name字段的数据类型应保持一致。

3、我看到你在子查询里反复折腾一张表ba_bgt_info_hz，这SQL其实还有优化空间的，比如，ba_bgt_info_hz.importtype如有索引，可以先提取出来作为子查询的第一个临时表，或者如果业务常用，可以创建为视图。

兄弟，写sql一般从里到外一步步写，每一步写完都要验证一下，这样检查错误是最简单方便的

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 以下回答来自chatgpt:

  我需要查看具体的报错信息才能帮助您解决问题。请提供报错信息或者更详细的情况。

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^

INSERT INTO tab1
SELECT id, '资金a' FROM temp_table;

第二列输入的是想要写的名称字符串“资金a”，因为该值在每行中都是相同的。

temp_table 中包含重复的 id 值，则在插入 tab1 表时可能会出现主键冲突错误。如果要防止这种情况，请在插入之前根据需要进行必要的数据清洗和去重。

基于最新版ChatGPT4的回答，望采纳!!!有其他问题也可以询问我哦、”(最新版更智能，功能更加强大)

以下是一个可能的修正后的SQL：

with tb1 as (
    select distinct m.track_pro_code 
    from ba_bgt_info_hz m 
    where m.ori_bgt_id in (
        select t.bgt_id 
        from ba_bgt_info_hz t 
        where t.track_pro_code in (
            select distinct track_pro_code 
            from ba_bgt_info_hz 
            where bgt_doc_title like '%车辆购置税收入补助地方资金%' 
                and is_deleted=2 
                and track_pro_code is not null 
                and importtype not in (8,9)
        ) 
        and t.billstatus>=0 
        and t.importtype not in (8,9) 
    )  
    and m.importtype not in (8,9)  
    union  
    select distinct track_pro_code 
    from bawith tb1 as (
    select distinct m.track_pro_code 
    from ba_bgt_info_hz m 
    where m.ori_bgt_id in (
        select t.bgt_id 
        from ba_bgt_info_hz t 
        where t.track_pro_code in (
            select distinct track_pro_code 
            from ba_bgt_info_hz 
            where bgt_doc_title like '%车辆购置税收入补助地方资金%' 
                and is_deleted=2 
                and track_pro_code is not null 
                and importtype not in (8,9)
        ) 
        and t.billstatus>=0 
        and t.importtype not in (8,9) 
    )  
    and m.importtype not in (8,9)  
    union  
    select distinct track_pro_code 
    from ba_bgt_info_hz 
    where bgt_doc_title like '%车辆购置税收入补助地方资金%' 
        and is_deleted=2 
        and track_pro_code is not null 
        and importtype not in (8,9)
)
insert into t_track_pro_code (track_pro_code, track_pro_code_name)
select track_pro_code, '车辆购置税收入补助地方资金' 
from tb1

这段SQL首先创建了一个名为tb1的临时表，然后在insert语句中从tb1中选择track_pro_code，并为所有行赋予相同的track_pro_code_name值【11†source】。

回答部分参考、引用ChatGpt以便为您提供更准确的答案：

根据您的需求，您可以修改SQL代码，将临时表的查询结果与固定的名称进行组合。下面是修改后的代码：

insert into tab1
select t.track_pro_code || ' 资金a' || (row_number() over (order by t.track_pro_code)) || ' 资金a', '名称'
from (
    with tb1 as (
        select distinct m.track_pro_code
        from ba_bgt_info_hz m
        where m.ori_bgt_id in (
            select t.bgt_id
            from ba_bgt_info_hz t
            where t.track_pro_code in (
                select distinct track_pro_code
                from ba_bgt_info_hz
                where bgt_doc_title like '%车辆购置税收入补助地方资金%'
                and is_deleted = 2
                and track_pro_code is not null
                and importtype not in (8, 9)
            )
            and t.billstatus >= 0
            and t.importtype not in (8, 9)
        )
        and m.importtype not in (8, 9)
        union
        select distinct track_pro_code
        from ba_bgt_info_hz
        where bgt_doc_title like '%车辆购置税收入补助地方资金%'
        and is_deleted = 2
        and track_pro_code is not null
        and importtype not in (8, 9)
    )
    select distinct track_pro_code
    from tb1
) t;

在修改后的代码中，我使用了窗口函数 row_number() 来生成一个递增的序列号，将其与查询结果进行组合，并添加固定的名称。这样可以达到您所期望的结果，例如：1234 资金a1 资金a、9876 资金a2 资金a 等。

请注意，修改后的代码仅供参考，请根据您的具体情况进行适当调整。如果您需要更详细的帮助或有其他需求，可以提供更多相关信息，以便我能够给出更准确的解决方案。