?????? 在做会员管理模块的时候，有这样一个需求，预存分为许多期，列表展示时需要查询出上期余额，本期余额，日报表倒是很好办，因为数据库中已经存了上期余额，关键是按月查询时，上期余额是上个月的最后一天的上期余额。
后来看了Oracle数据库中的函数，写出以下语句就搞定：
<select id="queryMerchantPrepayByMonth" parameterClass="java.util.HashMap" resultMap="merchantPrepayMap"> select to_char(to_date(day,'yyyy-mm-dd'),'yyyy-mm') as transTime,( select PREVIOUS_BALANCE_AMOUNT/100 as PREVIOUS_BALANCE_AMOUNT from report_account_prepay_statis where day = to_char( last_day( add_months(to_date(raps.month,'yyyy-mm'),-1) ) ,'yyyy-mm-dd') and store_id = 'merchant' and merchant_id =#merchantId# ) as PREVIOUS_BALANCE_AMOUNT , ( select BALANCE_AMOUNT/100 as BALANCE_AMOUNT from report_account_prepay_statis where day = ( case when raps.month < substr(#endDate#,1,7) then to_char( last_day( to_date(raps.month,'yyyy-mm') ) ,'yyyy-mm-dd' ) else #endDate# end ) and store_id = 'merchant' and merchant_id = #merchantId# ) as BALANCE_AMOUNT,sum(PREPAY_AMOUNT/100) as PREPAY_AMOUNT ,sum(CONSUME_AMOUNT/100) as CONSUME_AMOUNT ,sum(OVERDUE_AMOUNT/100) as OVERDUE_AMOUNT,sum(DELAY_AMOUNT/100) as DELAY_AMOUNTfrom report_account_prepay_statis rapswhere merchant_id=#merchantId# and store_id='merchant'and to_char(to_date(day,'yyyy-mm-dd'),'yyyy-mm-dd') between #startDate# and #endDate#group by to_char(to_date(day,'yyyy-mm-dd'),'yyyy-mm'),monthorder by month</select>
?
?
也就是day =
????? to_char(
????? last_day( add_months(to_date(raps.month,'yyyy-mm'),-1) )
????? ,'yyyy-mm-dd')
最关键了。
这里的raps.month是一个VARCHAR2类型的数值。举例说明，假如这个值是2011-01，那么它的结果是2010-12-31号，
select to_char( last_day( add_months(to_date('2011-01','yyyy-mm'),-1) ) ,'yyyy-mm-dd') from dual?