ORACLE查询语句求助

Question

题目：
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

Answer 1

打开Oracle SQL Developer, 选择 Tools --> Preferences --> Database --> NLS-->Date Format,
把默认的格式：DD-MON-RR 改为你想要的格式：
比如：显示完整的日期
将 ‘DD-MON-RR ’ 改为 ‘YYYY-MM-DD HH24:MI:SS’ 即可。

Answer 2

select lag(lr,1,0) over(order by rq )+cz,lr,cz,rq,sr,zc from (
(select (lag(sr-zc,1,0) over(order by rq,sr,zc))+sr-zc lr,sr-zc cz,t.*   from t6 t)) 无法实现日期连续

Answer 3

不好意思，没仔细看题WITH t AS
  ( 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