GROUP BY dates1
date1不能是外层查询字段的别名
date1不能是外层查询字段的别名
解决方案 »
- 如何查询正在运行的session运行的哪个存储过程或者JOB?
- 求问一句sql算controlfile大小的意思
- oracle database links 查询问题
- sql优化问题
- oracle和access如何实现数据互相导入导出?
- 急:在as4u2上oracm找不到hangcheck-timer
- msvc++6开发CS架构 : 服务器端接收请求报文后,如何最大效率地进行oracle数据库中操作,例如insert,select,update等? oci?
- 导出非常成功,但导入总是失败!请指教!
- 怎样在 P4(CPU)、Windows 2000 Server 下安装 Oracle 8i?
- 如果一个oracle数据库实例一直被一个程序进行读写占用,那么其他的程序是不是因为连接被占用而不能对数据库进行读写操作了?
- 这个程序包到底哪错了,一直报错
- 高分求一解决方案,服务器总是down机,一直报资源未释放,jdbc连接池满池
select t2.*,TO_CHAR(t2.dates,'YYYY-MM-DD') dates1 from (select rownum r,t1.* ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') dates
from USERACCOUNT t1
where rownum<18 AND CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') and TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
) t2
where t2.r>15
GROUP BY dates1
ORDER BY dates1;这样的也不行
select t2.* from
(select rownum r,t1.* ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') dates
from USERACCOUNT t1
where rownum<18 AND CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') and TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
) t2
where t2.r>15
GROUP BY dates
ORDER BY dates;字查询里别名也不行,而且子查询是能查到信息的,就是不能GROUP BY dates ,提示:[Err] ORA-00979: not a GROUP BY expression
2,“select t2.*,TO_CHAR(t2.dates,'YYYY-MM-DD') dates1 from”,除了聚合函数外,select 后只能出现GROUP BY后有的字段
select t2.* from
(select rownum r,t1.* ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') dates
from USERACCOUNT t1
where rownum<18 AND CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') and TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
) t2
where t2.r>15
select t2.* from
(select rownum r,t1.* ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') dates
from USERACCOUNT t1
where rownum<18 AND CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') and TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
) t2
where t2.r>15
GROUP BY dates
ORDER BY dates;字查询里别名也不行,而且子查询是能查到信息的,就是不能GROUP BY dates ,提示:[Err] ORA-00979: not a GROUP BY expression
子查询能查到信息是正常的,你这里的GROUP BY报错,你应该再认真看5楼的第二点
select t2.dates1 from (select rownum r,t1.* ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') dates1
from USERACCOUNT t1
where rownum<18 AND CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') and TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
) t2
where t2.r>15
GROUP BY dates1
ORDER BY dates1;
FROM (
SELECT T.*, ROWNUM R
FROM (
SELECT COUNT(T1.CREATETIME ) ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') DATES
FROM USERACCOUNT
T1
WHERE ROWNUM<100 AND CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') AND TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
GROUP BY TO_CHAR(T1.CREATETIME,'YYYY-MM-DD')
ORDER BY TO_CHAR(T1.CREATETIME,'YYYY-MM-DD')
)T
)T2 WHERE T2.R>15最终答案