写了以下语句
select ww.xmzw,ww.ksmc,qq.ysxm,ww.sl,ww.je from ysk qq,(select a.ksmc,b.sl,b.je,b.ysdm,b.xmzw from ks a,(select xmzw,sum(fysl) as sl,sum(jine) as je,ksdm,ysdm from cfk where sfrq>=to_date('2009-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and sfrq<to_date('2009-09-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and xmdm='01123' and (delbz='0' or delbz='5' or delbz='3') group by ysdm,ksdm,xmzw) b where a.ksdm(+)=b.ksdm) ww where qq.ysgh(+)=ww.ysdm order by ww.je desc;之后想将上面的语句改成以下格式~
select cfk.xmzw,
ks.ksmc,
cfk.ysdm,
sum(fysl),
sum(jine)
from cfk,
ks
where ( sfrq>=to_date('2009-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and sfrq<to_date('2009-09-30 00:00:00','yyyy-mm-dd hh24:mi:ss') ) and
(xmdm ='01123') and
(delbz='0' or delbz='5' or delbz='3') and
ks.ksdm(+)=cfk.ksdm
group by ysdm,ksdm,xmzw;但在PB运行的时候就一直出错~请大家指导下应该如何改才对
上面是还没有写完整的~请高手指点下应该如何完整的改
select ww.xmzw,ww.ksmc,qq.ysxm,ww.sl,ww.je from ysk qq,(select a.ksmc,b.sl,b.je,b.ysdm,b.xmzw from ks a,(select xmzw,sum(fysl) as sl,sum(jine) as je,ksdm,ysdm from cfk where sfrq>=to_date('2009-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and sfrq<to_date('2009-09-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and xmdm='01123' and (delbz='0' or delbz='5' or delbz='3') group by ysdm,ksdm,xmzw) b where a.ksdm(+)=b.ksdm) ww where qq.ysgh(+)=ww.ysdm order by ww.je desc;之后想将上面的语句改成以下格式~
select cfk.xmzw,
ks.ksmc,
cfk.ysdm,
sum(fysl),
sum(jine)
from cfk,
ks
where ( sfrq>=to_date('2009-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and sfrq<to_date('2009-09-30 00:00:00','yyyy-mm-dd hh24:mi:ss') ) and
(xmdm ='01123') and
(delbz='0' or delbz='5' or delbz='3') and
ks.ksdm(+)=cfk.ksdm
group by ysdm,ksdm,xmzw;但在PB运行的时候就一直出错~请大家指导下应该如何改才对
上面是还没有写完整的~请高手指点下应该如何完整的改
select cfk.xmzw, ks.ksmc, cfk.ysdm, sum(fysl), sum(jine) from cfk, ks
where (sfrq >= to_date('2009-07-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and sfrq < to_date('2009-09-30 00:00:00',
'yyyy-mm-dd hh24:mi:ss')--) 这个是不是多的啊???
and (xmdm = '01123')
and (delbz = '0' or delbz = '5' or delbz = '3')
and ks.ksdm(+) = cfk.ksdm
group by ysdm, ksdm, xmzw;
SELECT cfk.xmzw,
ks.ksmc,
cfk.ysdm,
SUM(fysl),
SUM(jine),
ks.ksmc
FROM cfk, ks
WHERE cfk.sfrq>=TO_DATE('2009-07-01 00:00:00','yyyy-mm-ddhh24:mi:ss')
AND cfk.sfrq <TO_DATE('2009-09-30 00:00:00','yyyy-mm-ddhh24:mi:ss')
AND cfk.xmdm ='01123'
AND(cfk.delbz='0' OR cfk.delbz='5' OR cfk.delbz='3')
AND ks.ksdm(+)=cfk.ksdm
GROUP BY cfk.ysdm,cfk.ksdm,cfk.xmzw,ks.ksmc;
这里请指定是哪个表的字段,修改为
cfk.xmzw,
ks.ksmc,
cfk.ysdm
SELECT temp1.xmzw,
temp1.ksmc,
temp1.ysxm,
SUM(temp1.fysl) AS fysl,
SUM(temp1.jine) AS jine
FROM (
SELECT ysk.ysxm AS ysxm,
ks.ksmc AS ksmc,
cfk.fysl AS fysl,
cfk.jine AS jine,
cfk.xmzw AS xmzw,
cfk.ksdm AS ksdm
FROM ysk ,ks ,cfk
WHERE
AND (delbz='0'
OR delbz='5'
OR delbz='3')
AND cfk.sfrq>=TO_DATE('2009-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND cfk.sfrq <TO_DATE('2009-09-30 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND cfk.xmdm='01123'
AND ks.ksdm(+)=cfk.ksdm
AND ysk.ysgh(+)=ks.ysdm
) temp1
GROUP BY temp1.ysdm ,temp1.xmzw ,temp1.ksdm ;
DESC;
“非单组分组函数”问题那就是在group by的问题。
没有出现在group by 里面,或者你拼写错误了
楼上的已经帮忙解决了,请速度结贴。
GROUP BY 后面应该填全 没有SUM()的字段
ks.ksmc,
cfk.ysdm,
SUM(fysl),
SUM(jine),
ks.ksmc
FROM cfk, ks
WHERE cfk.sfrq>=TO_DATE('2009-07-01 00:00:00','yyyy-mm-ddhh24:mi:ss')
AND cfk.sfrq <TO_DATE('2009-09-30 00:00:00','yyyy-mm-ddhh24:mi:ss')
AND cfk.xmdm ='01123'
AND(cfk.delbz='0' OR cfk.delbz='5' OR cfk.delbz='3')
AND ks.ksdm(+)=cfk.ksdm
GROUP BY cfk.ysdm,cfk.ksdm,cfk.xmzw,ks.ksmc;