工作中的笔记

最新推荐文章于 2024-08-23 21:32:35 发布

阿拉斯猪饲养员

最新推荐文章于 2024-08-23 21:32:35 发布

阅读量191

点赞数

分类专栏：数据库 mysql 文章标签： myspan idtransmark s 工span idtransmark st

本文链接：https://blog.csdn.net/qq_33432559/article/details/53333337

版权

mysql 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据库

10 篇文章 0 订阅

订阅专栏

<!--定时作业-->
<!--添加释放记录-->
<insert id="automaticRelease1" >
    insert into zxzq_drawcash_record (status ,memberid , releasenum, securitiesnum, securitiesnum_after, time, securitiesid)
    select 2, h.memberid,
    TRUNCATE( if(r.securities > TRUNCATE(h.securitiesnum * 0.999,2), TRUNCATE(h.securitiesnum-r.securities,2),TRUNCATE(h.securitiesnum * 0.001,2)),2),
    h.securitiesnum,
    TRUNCATE(if(r.securities > TRUNCATE(h.securitiesnum * 0.999,2),r.securities,TRUNCATE(h.securitiesnum - TRUNCATE(
    if(r.securities > TRUNCATE(h.securitiesnum * 0.999,2),TRUNCATE(h.securitiesnum-r.securities,2) ,TRUNCATE(h.securitiesnum * 0.001,2)),2),2)),2),
    NOW(),r.securitiesid
    from zxzq_holding h ,zxzq_record r where h.securitiesid = r.securitiesid and r.price = r.realprice and h.securitiesnum > r.securities
</insert>

<update id="automaticRelease2">
    replace into zxzq_drawcash (memberid, securitiesnum) select h.memberid ,
    TRUNCATE(d.securitiesnum + sum(if(r.securities > TRUNCATE(h.securitiesnum*0.999,2),
    TRUNCATE(h.securitiesnum-r.securities,2),TRUNCATE(h.securitiesnum*0.001,2))),2)
    from zxzq_holding h inner join zxzq_drawcash d on d.memberid = h.memberid
    inner join zxzq_record r on r.price=r.realprice and h.securitiesnum > r.securities  and h.securitiesid=r.securitiesid group by h.memberid
</update>
<update id="automaticRelease3">
    update zxzq_holding h, zxzq_record r set h.securitiesnum =
    if( r.securities > TRUNCATE(h.securitiesnum * 0.999,2) , r.securities,  TRUNCATE(h.securitiesnum * 0.999,2)) where r.securitiesid = h.securitiesid
    and r.price = r.realprice
</update>

作为定时器的sql代码，通过spring定时任务来完成。


<task:annotation-driven />
<task:scheduled-tasks>
   <task:scheduled ref="securitiesController" method="automaticRelease" cron="0 0 0 ? * MON-FRI"/>
</task:scheduled-tasks>

还需要在securitiesController的automaticRelease
方法上面增加@Scheduled
注解。