求一个困惑我很久的sql

[fengyoujie]

数据库为oracle

table1 table2
date in out date num
2009-06-03 5 2 2009-06-01 20
2009-06-10 10
2009-06-15 5
2009-06-20 5
2009-06-25 5
2009-07-01 10


其中table2只有一行，而且日期是不大于table1的最小日期的。table2存到是一个先期的余额
要求查询的结果为：
result
date in out Balance -----Balance=（num+in-out）
2009-06-01 20
2009-06-03 5 2 23
2009-06-10 10 13
2009-06-15 5 18
2009-06-20 5 13
2009-06-25 5 8
2009-07-01 10 18


请问诸位如何写这样的一个sql语句。多谢了

[yuntongsf]

还真是挺难的

[welyngj]

使用分析函数就可以做到。

[zcs_1]

select date, in, out,sum(balance) over(order by date) balance from
(
select date1, null in, null out, num balance from table2
union all
select date, in, out, nvl(in,0)-nvl(out,0) balance from table1
);

[zcs_1]

[Quote=引用 3 楼 zcs_1 的回复:]
select date, in, out,sum(balance) over(order by date) balance from
(
select date1, null in, null out, num balance from table2
union all
select date, in, out, nvl(in,0)-nvl(out,0) balance from table1
);
[/Quote]

修改一下

select date, in, out,sum(balance) over(order by date) balance from
(
select date, null in, null out, num balance from table2
union all
select date, in, out, nvl(in,0)-nvl(out,0) balance from table1
);

[fengyoujie]

多谢 ，诸位热心人。我来试试

[老张-AI]

用sum函数

[ren32408]

SELECT D.INI, D.OUTI, SUM(D.IO) OVER(ORDER BY D.DAT ASC) BALANCE
FROM (SELECT T.DAT, T.INI, T.OUTI, (T.INI - T.OUTI + T.BALANCE) IO
FROM (SELECT DAT, 0 INI, 0 OUTI, NUM BALANCE
FROM TABLE2
UNION ALL
SELECT DAT, INI, OUTI, 0 BALANCE FROM TABLE1) T) D

[qin_phoenix]

sun() over(order by date1) 逐行累计计算

[ojuju10]

--参考
create table tt(id int);
insert into tt select 1 from dual;
insert into tt select 2 from dual;
insert into tt select 3 from dual;
insert into tt select 4 from dual;
insert into tt select 5 from dual;
insert into tt select 6 from dual;
insert into tt select 7 from dual;
insert into tt select 8 from dual;
insert into tt select 8 from dual;
insert into tt select 10 from dual;
commit;

select id,sum(id)over(order by id) from tt
group by id;

[ansheng83]

一个都不懂

[inthirties]

是不是发重复了呀。
下面个贴也是你发的吧
http://topic.csdn.net/u/20090707/14/ffdeae62-36a5-4452-9e9d-3e78559cb64f.html?19479

SQL> select cdate, cin, cout, cnum, (sum(nvl(cin,0)-nvl(cout, 0)+nvl(cnum, 0))
over(order by cdate)) balance from (select cdate, null cin, null cout, cnum from
table2 union all select cdate, cin, cout, null cnum from table1) t
order by t.cdate;


试试这个

CDATE CIN COUT CNUM BALANCE
-------------------- ---------- ---------- ---------- ----------
2009-06-01 20 20
2009-06-03 5 2 23
2009-06-10 10 13
2009-06-15 5 18
2009-06-20 5 13
2009-06-25 5 8
2009-07-01 10 18

已选择7行。

[inthirties]

[Quote=引用 4 楼 zcs_1 的回复:]
引用 3 楼 zcs_1 的回复:

select date, in, out,sum(balance) over(order by date) balance from
(
select date, null in, null out, num balance from table2
union all
select date, in, out, nvl(in,0)-nvl(out,0) balance from table1
);
[/Quote]

4楼的zcs_1大侠这个也可以。思路挺好的。

[白发程序猿]

高手真多

[zzyzgydotnet]

up