with a(transdate, summarycode, pubaccnum,hostsernum, transcode) as (
select a.transdate, trim(a.summarycode), a.pubaccnum, a.hostsernum, a.transcode from pb600 a where a.transdate >= '2021-05-01' and a.transdate <= '2021-05-31' and a.summarycode in ('8156','8116','8115','8150','8114','8110','8151','8109','8105','8120','8104','8143','8142','8003','8129','8138','8139','8014','8015','8017','8117','8016','8141','8009','8010','8132','8133','1534','8147','8123','2198','8157','8013')
)
select a.summarycode, (select x.summarydes from pb002 x where x.summarycode = a.summarycode) as summary,count(1) from a where a.summarycode in ('8156','8116','8115','8150','8114','1534','8147','8157','8139','8138','8143','8142','8013','8129','8138','8014','8015','8017','8117','8016','8141','8009','8010','8132','8133') group by a.summarycode
上面的 这个没问题 可以执行，但是想把执行的结果插入表里面就不行了
报错104 遇到非法符号 这个要怎么写呢？
你既然是insert报错,那么就应该把insert命令发出来呀。
使用with的时候,insert 要放到with前面去
create table test_20220123_01(a number);
insert into test_20220123_01(a)
with xx as (select 1 a from dual)
select a from xx;
你都能用sql查出数据了,为什么不直接用查询sql去插,还导出成值再插？
还有,你发的这个sql也不完整啊,指定了7个字段,那么后面的值也有接7个,并且按顺序一一对应。
你这后面括号里只有4个值,而且还是以逗号结尾,当然会报错。
正常的单行插入sql应该长这样
insert into 表名(字段1, 字段2, 字段7, 字段8 ) VALUES ( '字段1的值','字段2的值','字段7的值','字段8的值')
insert int re912 (TRANSDATE, CHANNEL, INSTCODE, SUBSYSID, SUMMARYCODE, FREEUSE7, COUNTS ) VALUES ( '2021-08-01','01','00000000','00'，
这么写 然后 后面再加一个） 就不行了
看起来说的是mysql数据库，我放下代码，你试一下吧：
insert int re912 (TRANSDATE, CHANNEL, INSTCODE, SUBSYSID, SUMMARYCODE, FREEUSE7, COUNTS )
with a(transdate, summarycode, pubaccnum,hostsernum, transcode) as (
select a.transdate, trim(a.summarycode), a.pubaccnum, a.hostsernum, a.transcode from pb600 a where a.transdate >= '2021-05-01' and a.transdate <= '2021-05-31' and a.summarycode in ('8156','8116','8115','8150','8114','8110','8151','8109','8105','8120','8104','8143','8142','8003','8129','8138','8139','8014','8015','8017','8117','8016','8141','8009','8010','8132','8133','1534','8147','8123','2198','8157','8013')
)
select 定值1,定值2,定值3,定值4,a.summarycode, (select x.summarydes from pb002 x where x.summarycode = a.summarycode) as summary,count(1) from a where a.summarycode in ('8156','8116','8115','8150','8114','1534','8147','8157','8139','8138','8143','8142','8013','8129','8138','8014','8015','8017','8117','8016','8141','8009','8010','8132','8133') group by a.summarycode
希望可以帮助到你。
您好，我是有问必答小助手，您的问题已经有小伙伴帮您解答，感谢您对有问必答的支持与关注！