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);
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;
如果使用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);
~~~~~~~~~
相同?