以下语句正常得到结果:
SELECT a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC ,count(1)
FROM TYJMX a,TJGB b,TXSJMCYB c
WHERE a.sljg=b.jgbh and C.YB=substr(a.tdj,1,4)
and a.sljg='31003800' and substr(a.tdj,1,4)<>'3100'
and a.yjzt<>'Z' and substr(a.pc,1,2)<>'CS'
group by a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC
; 但是: SELECT a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC ,count(1) ,
(SELECT COUNT(*)
FROM tyjmx d
WHERE d.sljg = a.sljg and d.tdj=a.TDJ
and d.sljg='31003800' and substr(d.tdj,1,4)<>'3100'
AND d.yjzl = 'B类邮件' )
FROM TYJMX a,TJGB b,TXSJMCYB c
WHERE a.sljg=b.jgbh and C.YB=substr(a.tdj,1,4)
and a.sljg='31003800' and substr(a.tdj,1,4)<>'3100'
and a.yjzt<>'Z' and substr(a.pc,1,2)<>'CS'
group by a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC
;却报错:RA-00979: not a GROUP BY expression
其中d.yjzl = 'B类邮件' ) 是邮件种类中的一部分
oracle10g是支持这种写法的,求助
SELECT a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC ,count(1)
FROM TYJMX a,TJGB b,TXSJMCYB c
WHERE a.sljg=b.jgbh and C.YB=substr(a.tdj,1,4)
and a.sljg='31003800' and substr(a.tdj,1,4)<>'3100'
and a.yjzt<>'Z' and substr(a.pc,1,2)<>'CS'
group by a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC
; 但是: SELECT a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC ,count(1) ,
(SELECT COUNT(*)
FROM tyjmx d
WHERE d.sljg = a.sljg and d.tdj=a.TDJ
and d.sljg='31003800' and substr(d.tdj,1,4)<>'3100'
AND d.yjzl = 'B类邮件' )
FROM TYJMX a,TJGB b,TXSJMCYB c
WHERE a.sljg=b.jgbh and C.YB=substr(a.tdj,1,4)
and a.sljg='31003800' and substr(a.tdj,1,4)<>'3100'
and a.yjzt<>'Z' and substr(a.pc,1,2)<>'CS'
group by a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC
;却报错:RA-00979: not a GROUP BY expression
其中d.yjzl = 'B类邮件' ) 是邮件种类中的一部分
oracle10g是支持这种写法的,求助
解决方案 »
- oracle 10g安装问题 在oraparam.ini中未找到先决条件检查
- oracleDBconsoleorcl不能启动
- SSIS 连接ORACLE 以日期为查询条件的问题,请大家帮忙!在线等待中
- oracle 中能不能判断这个字段中是数字还是字符
- Berkeley DB 怎样保证写1亿条数据.并且性能不下降
- PB4如何访问oracle8.16呢?(在线等)
- 怎样从 Oracle9i 的 iSQL*Plus 界面登陆?
- Oracle 11i是用什么机制实现的??
- 开发ORACLE用的最多的是什么语言?
- 求助,关于授权仍不能建表的问题
- pl/sql中job不能运行
- 怎么在standby database下新建与生产库一样实例名称的实例
FROM tyjmx d
WHERE d.sljg = a.sljg and d.tdj=a.TDJ
and d.sljg='31003800' and substr(d.tdj,1,4)<>'3100'
AND d.yjzl = 'B类邮件' )
这个弄个列别名
然后写到group by字句里面
如果添加别名,并加在groupby后面的话,执行就会提示:
ORA-00900: invalid SQL statement 别名
(SELECT COUNT(*)
FROM tyjmx d
WHERE d.sljg = a.sljg and d.tdj=a.TDJ
and d.sljg='31003800' and substr(d.tdj,1,4)<>'3100'
AND d.yjzl = 'B类邮件'
group by a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC)
FROM TYJMX a,TJGB b,TXSJMCYB c
WHERE a.sljg=b.jgbh and C.YB=substr(a.tdj,1,4)
and a.sljg='31003800' and substr(a.tdj,1,4)<>'3100'
and a.yjzt<>'Z' and substr(a.pc,1,2)<>'CS'
group by a.sljg,b.jgmc,substr(a.tdj,1,4) ,C.XSJMC加了红字部分,你试试。
谢谢,我这里报告ora-00979应该是误报,不是groupby的问题