select a.saccdate, Row_number() OVER(order BY a.saccdate desc)
from t_stockaccounting a
where
To_Char(a.saccdate,'yyyy-mm-dd') < To_Char(SYSDATE,'yyyy-mm-dd') and rownum <= 1
查询出来无效 invalid number,问题应该出在时间对比这里、该怎么写呢?
from t_stockaccounting a
where
To_Char(a.saccdate,'yyyy-mm-dd') < To_Char(SYSDATE,'yyyy-mm-dd') and rownum <= 1
查询出来无效 invalid number,问题应该出在时间对比这里、该怎么写呢?
from t_stockaccounting a
where
To_Char(a.saccdate,'yyyy-mm-dd') < To_Char(SYSDATE,'yyyy-mm-dd')
and rownum=1
order BY a.saccdate desc
提示 invalid number
2011-07-05
2011-07-08
2011-07-16
2011-07-06
2011-07-11 输入一个日期 要求返回这个日期最近的一条记录 比如 输入 2011-07-14号 返回 2011-07-11
[TEST@orcl] SQL>WITH t1 AS(
2 SELECT DATE'2011-07-05' logdate FROM dual UNION ALL
3 SELECT DATE'2011-07-08' logdate FROM dual UNION ALL
4 SELECT DATE'2011-07-16' logdate FROM dual UNION ALL
5 SELECT DATE'2011-07-06' logdate FROM dual UNION ALL
6 SELECT DATE'2011-07-11' logdate FROM dual
7 )SELECT *
8 FROM(
9 SELECT t1.*,row_number()over(ORDER BY t1.logdate DESC) row_
10 FROM t1
11 WHERE t1.logdate <= DATE'2011-07-14')
12 WHERE row_ = 1;LOGDATE ROW_
------------------- ----------
2011/07/11 00:00:00 1