select substr(to_char(update_time,'yyyy-mm-dd hh24:mi'),0,10) spec_day, count(*)
from tbl_error t
where spec_day="2009-09-27"
group by substr(to_char(update_time,'yyyy-mm-dd hh24:mi'),0,10)
我想利用update_time(默认值是sysdate) 判断某一天是否有数据插入表中,但上述语句的提示错误,where那一行,“spec_day标示符无效”。何解? 多谢!
这个不是用的时间格式,用的是字符串。要用to_date()吧
可spec_day已经是to_char后substr出来的了,不应该还是date型啊~~
select substr(to_char(update_time,'yyyy-mm-dd hh24:mi'),0,10) spec_day, count(*)
from tbl_error t
where spec_day='2009-09-27'
group by substr(to_char(update_time,'yyyy-mm-dd hh24:mi'),0,10)单引号
from tbl_error t
where substr(to_char(update_time,'yyyy-mm-dd hh24:mi'),0,10)="2009-09-27"
group by substr(to_char(update_time,'yyyy-mm-dd hh24:mi'),0,10)
好像where子句中不能用别名的问题
SQL> select substr(to_char(hire_date,'yyyy-mm-dd hh24:mi'),0,10) spec_day, count(*)
2 from employees t
3 group by substr(to_char(hire_date,'yyyy-mm-dd hh24:mi'),0,10)
4 having substr(to_char(hire_date,'yyyy-mm-dd hh24:mi'),0,10)='2009-09-27';no rows selected
可以优化一下,不用substr函数
select to_char(update_time,'yyyy-mm-dd' spec_day, count(*)
from tbl_error t
where to_char(update_time,'yyyy-mm-dd')='2009-09-27'
group by to_char(update_time,'yyyy-mm-dd'
双引号改成单引号
from tbl_error t
where update_time=date'2009-09-27'
group to_char(update_time,'yyyy-mm-dd')
也知道你结帖了
只是看你的写法觉得你没理解to_char的用法
随手改下