在做会员管理模块的时候,有这样一个需求,预存分为许多期,列表展示时需要查询出上期余额,本期余额,日报表倒是很好办,因为数据库中已经存了上期余额,关键是按月查询时,上期余额是上个月的最后一天的上期余额。
后来看了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_AMOUNT
from report_account_prepay_statis raps
where 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'),month
order 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