select a.value,a.date
from a
where rownum<=3
order by a.date desc这个sql运行的结果集是value date
3000 2012-07-07
2000 2012-07-06
1000 2012-07-05我想用(第一条记录value-最后一条value)/(日期差),也就是(3000-2000)/2=500,这个用sql怎么写?要求效率比较高的sql,不用存储过程。
from a
where rownum<=3
order by a.date desc这个sql运行的结果集是value date
3000 2012-07-07
2000 2012-07-06
1000 2012-07-05我想用(第一条记录value-最后一条value)/(日期差),也就是(3000-2000)/2=500,这个用sql怎么写?要求效率比较高的sql,不用存储过程。
(MAX(CASE WHEN ORDER_ID = 1 THEN DATA_DATE END) - MAX(CASE WHEN ORDER_ID = 3 THEN DATA_DATE END))
FROM (
SELECT value,DATA_DATE,ROW_NUMBER() OVER(ORDER BY DATA_DATE DESC) AS ORDER_ID
FROM (
SELECT 3000 AS value,TO_DATE('2012-07-07','YYYY-MM-DD') AS DATA_DATE FROM DUAL UNION ALL
SELECT 2000, TO_DATE('2012-07-06','YYYY-MM-DD') FROM DUAL UNION ALL
SELECT 1000, TO_DATE('2012-07-05','YYYY-MM-DD') FROM DUAL
) T
)
WHERE ORDER_ID IN (1,3)
(SELECT A.VALUE MIN_VALUE ,A.DATE MIN_DATE FROM A WHERE A.ROWNUM =1) A_MIN,
(SELECT MAX(A.ROWNUM),A.VALUE MAX_VALUE,A.DATE MAX_DATE FROM A GROUP BY A.VALUE,A.DATE) A_MAX
WHERE A_MIN.ROWNUM=A_MAX.ROWNUM