数据库为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语句。多谢了
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语句。多谢了
(
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
);
修改一下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
);
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
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;
下面个贴也是你发的吧
http://topic.csdn.net/u/20090707/14/ffdeae62-36a5-4452-9e9d-3e78559cb64f.html?19479SQL> 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行。
4楼的zcs_1大侠这个也可以。思路挺好的。