select a.hwcode ,sum(a.kcl)+sum(b.rkl)-sum(c.ckl) 结余量 ,(select (aa.pjjg*aa.kcl)+bb.rkjg*bb.rkl-cc.ckjg*cc.ckl)/(aa.kcl+bb.rkl-cc.ckl) from bwtabel aa,jinkutable bb,chukutable cc where aa.hwcode=bb.hwcode and aa.hwcode=cc.hwcode and aa.hwcode=a.hwcode ) 平均价格from hwtable a left join jinkutable b on a.hwcode=b.hwcode left join chukutable c on a.hwcode=c.hwcode group by a.hwcode,b.hwcode,c.hwcode
update hwtable set kcl = A.kcl, pjjg = A.pjjg from (select hwcode, sum(kcl) kcl, sum(kcl*pjjg)/sum(kcl) pjjg from (select hwcode, kcl, pjjg from hwtable union all select hwcode, rkl, rkjg from jinkutable union all select hwcode, ckl, -ckjg from chukutable ) B group by hwcode ) A where hwtable.hwcode = A.hwcode愉快的登山者 ⊙ ◢◣◢◣◢◣
select a.hwcode ,sum(a.kcl)+sum(b.rkl)-sum(c.ckl) 结余量 ,(select (aa.pjjg*aa.kcl)+bb.rkjg*bb.rkl-cc.ckjg*cc.ckl)/(aa.kcl+bb.rkl-cc.ckl) from bwtabel aa,jinkutable bb,chukutable cc where aa.hwcode=bb.hwcode and aa.hwcode=cc.hwcode and aa.hwcode=a.hwcode ) 平均价格from hwtable a left join jinkutable b on a.hwcode=b.hwcode left join chukutable c on a.hwcode=c.hwcode group by a.hwcode,b.hwcode,c.hwcode
,sum(a.kcl)+sum(b.rkl)-sum(c.ckl) 结余量
,(select (aa.pjjg*aa.kcl)+bb.rkjg*bb.rkl-cc.ckjg*cc.ckl)/(aa.kcl+bb.rkl-cc.ckl) from bwtabel aa,jinkutable bb,chukutable cc where aa.hwcode=bb.hwcode and aa.hwcode=cc.hwcode and aa.hwcode=a.hwcode ) 平均价格from hwtable a left join jinkutable b on a.hwcode=b.hwcode
left join chukutable c on a.hwcode=c.hwcode
group by a.hwcode,b.hwcode,c.hwcode
(select hwcode, sum(kcl) kcl, sum(kcl*pjjg)/sum(kcl) pjjg from
(select hwcode, kcl, pjjg from hwtable
union all
select hwcode, rkl, rkjg from jinkutable
union all
select hwcode, ckl, -ckjg from chukutable
) B
group by hwcode ) A
where hwtable.hwcode = A.hwcode愉快的登山者 ⊙
◢◣◢◣◢◣
,sum(a.kcl)+sum(b.rkl)-sum(c.ckl) 结余量
,(select (aa.pjjg*aa.kcl)+bb.rkjg*bb.rkl-cc.ckjg*cc.ckl)/(aa.kcl+bb.rkl-cc.ckl) from bwtabel aa,jinkutable bb,chukutable cc where aa.hwcode=bb.hwcode and aa.hwcode=cc.hwcode and aa.hwcode=a.hwcode ) 平均价格from hwtable a left join jinkutable b on a.hwcode=b.hwcode
left join chukutable c on a.hwcode=c.hwcode
group by a.hwcode,b.hwcode,c.hwcode