求一个sql语句

Question

数据库为oracletable1                                            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语句。多谢了

Answer 1

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行。

Answer 2

由于 date, in, out都是oracle的关键字，所以我都用cdate, cin, cout和cnum代替了你表里的字段。

Answer 3

自己写了下！试试！
select t.alldate CDATE,
       t.ina CIN,
       t.outa COUT,
       t.num CUNM,
       (sum(nvl(t.ina, 0) + nvl(t.num, 0) - nvl(t.outa,0)) over(order by t.alldate)) BALANCE
  from (select nvl(a.sdate, b.sdate) alldate,
               a.ina,
               a.outa,
               b.num
          from t1 a
          full outer join t2 b on a.sdate = b.sdate) tCDATE      CIN COUT CUNM BALANCE
2009-6-1         20       20
2009-6-3       5   2         23
2009-6-10     10        13
2009-6-15      5              18
2009-6-20     5         13
2009-6-25     5         8
2009-7-1       10           18

Answer 4

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;

Answer 5

没测试过，看看思路对不对！
select t1.date, t1.in, t1.out, (t1.in + sum(t2.in) - (t1.out + sum(t2.out)) + t3.num) as Balance
from table1 t1, table1 t2, table2 t3
where t1.date >= t2.date
group by t1.date, t1.in, t1.out, t3.num

Answer 6

上面的 where t1.date >= t2.date
改为
where t1.date > t2.date (+)

Answer 7

这个结果是正确的select date,
'' as in,
'' as out,
num
from table2
union all
select t1.date,
t1.in, 
t1.out,
(sum(nvl(t2.in,0)) - sum(nvl(t2.out,0)) + t3.num) as Balance 
from table1 t1, table1 t2, table2 t3 
where t1.date >= t2.date 
group by t1.date, t1.in, t1.out, t3.num date            in      out     num
20090601 0 0 20
20090603 5 2 23
20090610 0 10 13
20090615 5 0 18
20090620 0 5 13
20090625 0 5 8
20090701 10 0 18