应用开发笔记
0%

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日的行程都没有，有高人知道怎么做么？