impossible !1. U have to describle your business logic in details
2. U have to create stored procedure and function to realize your business logic remember : writing good sql doesn't mean to write everything in a single sql .
2. U have to create stored procedure and function to realize your business logic remember : writing good sql doesn't mean to write everything in a single sql .
解决方案 »
- ORACLE: 从emp中查询工资最高的三位员工信息
- SQL语句写入存贮过程,缺插入不了数据,帮忙看看什么原因?
- 高发求救:解决PL/SQL Developer中表结构注释都显示 "?"问题
- ORACLE 表的CHECK约束问题
- 有難度的SQL語句
- power designer中有字段相同的时候,改变1张表中那个字段,另张表的也变,怎样不改变啊?
- oracle9i安装问题?
- 请问ORA-12571:TNS packet writer failure. 写入数据包失败是什么原因????
- 各位,多指教!!2000 pro 上怎么就装不上个oracle 9i
- 我宣布退出兄弟会
- 在线等待!
- Oracle9中PL SQL存储过程写文件的字符集问题,急求答案,解决后分数任意!
http://expert.csdn.net/Expert/topic/2230/2230287.xml?temp=.630932
当时我回答的有些草率,下面是整理后的答案:only use sql not use user-define function!TABLE如下
日期 收入 支出
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10能否用SELECT語句得出以下結果
1.
日期 收入 支出 餘額
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/5 60 10 552.
TABLE加入一行
2000/3/5 60 10
結果
日期 收入 支出 餘額
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/5 120 20 1053.
日期 收入 支出 餘額
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/3 0 0 5
2000/3/4 0 0 5
SQL> select * from inout;THEDATE INCOME OUTGO
--------- ---------- ----------
01-MAR-00 50 30
02-MAR-00 45 60
05-MAR-00 60 10--- 问题一:SQL> select thedate,income,outgo,
2 sum(income-outgo) over(order by thedate) surplus
3 from inout
4 /THEDATE INCOME OUTGO SURPLUS
--------- ---------- ---------- ----------
01-MAR-00 50 30 20
02-MAR-00 45 60 5
05-MAR-00 60 10 55--- 问题二:
SQL> insert into inout values(to_date('2000-3-5','yyyy-mm-dd'),60,10);1 row created.SQL> select thedate,sum(income),sum(outgo),
2 sum(sum(income)-sum(outgo)) over(order by thedate) surplus
3 from inout
4* group by thedate
SQL> /THEDATE SUM(INCOME) SUM(OUTGO) SURPLUS
--------- ----------- ---------- ----------
01-MAR-00 50 30 20
02-MAR-00 45 60 5
05-MAR-00 120 20 105--- 问题三:SQL> select a.thedate,sum(nvl(income,0)),sum(nvl(outgo,0)),
2 sum(sum(income)-sum(outgo)) over(order by a.thedate) add_surplus
3 from (select rownum + ( select min(thedate) from inout ) -1 thedate from inout) a,
4 inout b
5 where a.thedate = b.thedate(+)
6* group by a.thedate
SQL> /THEDATE SUM(NVL(INCOME,0)) SUM(NVL(OUTGO,0)) ADD_SURPLUS
--------- ------------------ ----------------- -----------
01-MAR-00 50 30 20
02-MAR-00 45 60 5
03-MAR-00 0 0 5
04-MAR-00 0 0 5