题目:
Table如下:
日期 收入 支出
2008/8/1 50 30
2008/8/2 45 60
2008/8/5 60 10
用SELECT语句得出以下結果
日期 收入 支出 余额
2008-AUG-01 50 30 20
2008-AUG-02 45 60 5
2008-AUG-03 0 0 5
2008-AUG-04 0 0 5
2008-AUG-05 60 10 55
Table如下:
日期 收入 支出
2008/8/1 50 30
2008/8/2 45 60
2008/8/5 60 10
用SELECT语句得出以下結果
日期 收入 支出 余额
2008-AUG-01 50 30 20
2008-AUG-02 45 60 5
2008-AUG-03 0 0 5
2008-AUG-04 0 0 5
2008-AUG-05 60 10 55
把默认的格式:DD-MON-RR 改为你想要的格式:
比如:显示完整的日期
将 ‘DD-MON-RR ’ 改为 ‘YYYY-MM-DD HH24:MI:SS’ 即可。
(select (lag(sr-zc,1,0) over(order by rq,sr,zc))+sr-zc lr,sr-zc cz,t.* from t6 t)) 无法实现日期连续
( SELECT '2008/8/1' AS fDATE, 50 AS income, 30 AS fee FROM dual
UNION ALL
SELECT '2008/8/2', 45, 60 FROM dual
UNION ALL
SELECT '2008/8/5', 60, 10 FROM dual
)
SELECT fdate,
income,
fee,
SUM(remain)over(order by fdate) AS remain
FROM
(SELECT a.fdate,
NVL(income,0) AS income,
NVL(fee,0) AS fee,
NVL(income,0)-NVL(fee,0) AS remain
FROM
(SELECT to_date('2008-08-01' , 'YYYY-MM-DD') + ROWNUM - 1 fdate
FROM dual
CONNECT BY ROWNUM <= (to_date('2008-08-05' , 'YYYY-MM-DD') - to_date('2008-08-01' , 'YYYY-MM-DD') + 1)
) a
LEFT JOIN
(SELECT to_date(fdate,'yyyy/mm/dd') AS fDate,income,fee FROM t
) b
ON a.fdate = b.fdate
ORDER BY a.fdate
)--结果FDATE INCOME FEE REMAIN
------------------------- ---------------------- ---------------------- ----------------------
2008-08-01 00:00:00 50 30 20
2008-08-02 00:00:00 45 60 5
2008-08-03 00:00:00 0 0 5
2008-08-04 00:00:00 0 0 5
2008-08-05 00:00:00 60 10 55