可以用 insert into table(...) select ... sum(.) ... 实现吧看你具体要求,说详细点。
insert into chcpckhzb(kjn,kjy,ckbm,ckdh,khbm,cpbm,cksl) select a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm,sum(b.sl) from ckcpckd a,ckcpckdmxb b where a.ckdbh=b.ckdbh and a.dzkjn=i_year and a.dzkjy=i_month and b.yjssl=0 group by (a.ckbm,a.ckdbh,a.ckdbh,b.wlbm);那位大哥帮我检查一下有什么语法错误。
如果使用GROUP BY 则SELECT 后的字段必须要在GROUP BY 后出现,或者写出聚合函数的形式 :SUM,MAX,AVG,等等。insert into chcpckhzb(kjn,kjy,ckbm,ckdh,khbm,cpbm,cksl) select a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm, ~~~~~~~~~~ ~~~~~ 这三个字段不是分组项,也不是聚合函数的形式! sum(b.sl) from ckcpckd a,ckcpckdmxb b where a.ckdbh=b.ckdbh and a.dzkjn=i_year and a.dzkjy=i_month and b.yjssl=0 group by (a.ckbm,a.ckdbh,a.ckdbh,b.wlbm); ~~~~~~~~~ 相同?
同意CHENGXB(大山),这样写试试:insert into chcpckhzb(kjn,kjy,ckbm,ckdh,khbm,cpbm,cksl) select a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm,sum(b.sl) from ckcpckd a,ckcpckdmxb b where a.ckdbh=b.ckdbh and a.dzkjn=i_year and a.dzkjy=i_month and b.yjssl=0 group by a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm;
insert into table(...) select ... sum(.) ... 实现吧看你具体要求,说详细点。
select a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm,sum(b.sl)
from ckcpckd a,ckcpckdmxb b
where a.ckdbh=b.ckdbh and
a.dzkjn=i_year and
a.dzkjy=i_month and
b.yjssl=0
group by (a.ckbm,a.ckdbh,a.ckdbh,b.wlbm);那位大哥帮我检查一下有什么语法错误。
select a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm,
~~~~~~~~~~ ~~~~~
这三个字段不是分组项,也不是聚合函数的形式!
sum(b.sl)
from ckcpckd a,ckcpckdmxb b
where a.ckdbh=b.ckdbh and
a.dzkjn=i_year and
a.dzkjy=i_month and
b.yjssl=0
group by (a.ckbm,a.ckdbh,a.ckdbh,b.wlbm);
~~~~~~~~~
相同?
select a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm,sum(b.sl)
from ckcpckd a,ckcpckdmxb b
where a.ckdbh=b.ckdbh and
a.dzkjn=i_year and
a.dzkjy=i_month and
b.yjssl=0
group by a.dzkjn,a.dzkjy,a.ckbm,a.ckdbh,a.ckdwbm,b.wlbm;
最好对统计数据进行一定的处理比如统计为空的话可以设置为0
nvl(sum(sl),0)
统计使用的函数有count, sum, max, min, agv
只是统计还是有些问题
给分