Mysql 日期函数以及IF条件语句的使用

现在在做的项目需要有个日程管理,日程分四种周期,单次,每天,每周,每月,表结构如下图:

涉及到的字段就两个,一个是period,存储周期类型,值为0-4,0单次,1每天,2每周,3每月.
另一个是data,存储周期对应的数据,如果是单次,这里就是具体日期,如果是每天,这里为空,如果是每周,值为1-7,如果是每月,值为1-31.
我现在的问题是,要把最近几天的行程从数据库读出来,然后按日期排序,因为是循环的日程,所以除了单次日程外,不可能在数据库中存储具体日期,只能根据周期类型以及周期的数据,再根据今天的日期来计算该行程下次执行的时间。因为要根据日期排序,所以除非把数据库所有记录都读出来,否则不可能用PHP来排序,但是把所有记录读出来,明显不现实。
下面就是用Mysql自带的函数计算每个行程的下一次执行日期,返回再排序.


select if(period=2,ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+ if(WEEKDAY(CURDATE())>data-1,data+6,data-1)),
if(period=0,data,if(period=1,CURDATE(),if(DAYOFMONTH(CURDATE())<=data,if(ADDDATE(CURDATE(),
-DAYOFMONTH(CURDATE())+data)>last_day(curdate()),ADDDATE(DATE_ADD(CURDATE(), INTERVAL 1 MONTH ),
-DAYOFMONTH(CURDATE())+data),ADDDATE(CURDATE(),-DAYOFMONTH(CURDATE())+data)), ADDDATE(DATE_ADD(CURDATE(),
 INTERVAL 1 MONTH ),-DAYOFMONTH(CURDATE())+data))))) as date,id,routeId,data,period from route_data order by date

这行语句花费我整整半天的时间,是我写过的最复杂的SQL语句,如果您有优化简短的写法,请留言。
主要用到了IF判断,日期计算,几个函数的用法自己搜索一下吧,还是蛮简单的。
上面的代码还有一个小问题,在计算月周期的时候,如果今天是1月31号,而保存的周期数据是30,上面的代码计算方法是月份加1,就是2月30日,明显不存在2月30日,而DATE_ADD函数会自动调整了2月的最后一天,2月28日,或2月29日,我们设定的是每个月的30日触发,所以返回的结果不符合。但这个问题不影响,因为我要的结果仅仅是最近1周的行程,如果有需要,可以参考上面lasy_day函数的用法,判断该月最后一天,再用if语句。上用的lasy_day判断,因为一个小月后,跟着肯定是大月,所以如果计算出的日期比当月最后一天晚,这个月肯定是小月,月份加1即可,不用再判断。
上面的语句没有where,不能限定查询的结果在最近几天内,因为where语句优先级高于select,而date字段其实是在select时生成的,所以不能直接where date<'2012-05-01'这样写,必须在where中也计算日期,做对比。


select if(period=2,ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+ if(WEEKDAY(CURDATE())>data-1,data+6,data-1)),
if(period=0,data,if(period=1,CURDATE(),if(DAYOFMONTH(CURDATE())<=data,if(ADDDATE(CURDATE(),
-DAYOFMONTH(CURDATE())+data)>last_day(curdate()),ADDDATE(DATE_ADD(CURDATE(), INTERVAL 1 MONTH ),
-DAYOFMONTH(CURDATE())+data),ADDDATE(CURDATE(),-DAYOFMONTH(CURDATE())+data)), ADDDATE(DATE_ADD(CURDATE(), 
INTERVAL 1 MONTH ),-DAYOFMONTH(CURDATE())+data))))) as date,id,routeId,data,period from route_data where (if(period=2,ADDDATE(CURDATE(),
-WEEKDAY(CURDATE())+ if(WEEKDAY(CURDATE())>data-1,data+6,data-1)),if(period=0,data,if(period=1,CURDATE(),
if(DAYOFMONTH(CURDATE())<=data,if(ADDDATE(CURDATE(),-DAYOFMONTH(CURDATE())+data)>last_day(curdate()),ADDDATE(DATE_ADD(CURDATE(),
 INTERVAL 1 MONTH ),-DAYOFMONTH(CURDATE())+data),ADDDATE(CURDATE(),-DAYOFMONTH(CURDATE())+data)),
 ADDDATE(DATE_ADD(CURDATE(), INTERVAL 1 MONTH ),-DAYOFMONTH(CURDATE())+data))))))<'2012-05-01'

限定日期在5.1前,还有个问题,暂时找不到方法解决。就是如果该日程的下下一个执行时间也在限定日期内,如何返回?比如说,每天都执行的日程,上面的方法虽然限定在5.1前,但只会返回今天的行程,27,28,29,30日的行程都没有,有高人知道怎么做么?

© 2012, 冰冻鱼. 请尊重作者劳动成果,复制转载保留本站链接! 应用开发笔记