Ibatis中求上个月最后一天的实例sql语句

最新推荐文章于 2024-11-09 21:56:55 发布

原创最新推荐文章于 2024-11-09 21:56:55 发布 · 144 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #iBATIS #Oracle

Ibatis 专栏收录该内容

4 篇文章

订阅专栏

在做会员管理模块的时候，有这样一个需求，预存分为许多期，列表展示时需要查询出上期余额，本期余额，日报表倒是很好办，因为数据库中已经存了上期余额，关键是按月查询时，上期余额是上个月的最后一天的上期余额。

后来看了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 &lt; 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