已知myaccount_no, mydate1, mydate2select x.account_no, x.amount - nvl(y.amount,0) difference
from table1 x, table1 y
where x.account_no = y.account_no
and x.account_no = myaccount_no
and x.date1 = mydate1
and y.date1 = mydate2
from table1 x, table1 y
where x.account_no = y.account_no
and x.account_no = myaccount_no
and x.date1 = mydate1
and y.date1 = mydate2
from table1 x, table1 y
where x.account_no = y.account_no (+)
and x.account_no = myaccount_no
and x.date1 = mydate1
and y.date1 = mydate2
你说的是余额表,若一个账户9月10日有余额为1000,但9月11日没有记录,需要取8月12至9月11日的余额增长数时,9月11日的余额是按零算,还是按9月10日的余额1000来算?既然是余额表,我想应该是按最后的余额来算当日的余额的(上面例子中就是按1000来算).
MS SQL SERVER:
参数@acctno,@begindate,@enddateselect facctno,t2.fbalance-isnull(t1.fbalance,0) as fdiff
from
(
select top 1 facctno,fdate,fbalance
from tablename
where facctno=@acctno and fdate<=@begindate
order by fdate desc) t1
right join
(select top 1 facctno,fdate,fbalance
from tablename
where facctno=@acctno and fdate<=@enddate
order by fdate desc) t2
on t2.facctno=t1.facctno