需求:
一个活动表,记录有开始时间字段start_time,结束时间字段end_time
需要分页获取全部字段,总体上:
- 1.正在进行的活动排在最前部分(start_time < now < end_time)
这部分内部,按结束时间升叙,也就是即将结束的活动在前面。
- 2.即将开始的活动排在中间部分(now < start_time);
这部分内部,按开始时间升序,也就是即将开始的活动在前面。
- 3.已经过期的活动在最后部分(end_time < now)。
这部分内部,按结束时间降序,也就是最近结束的活动在前面。
分析:
根据条件,将(开始时间,结束时间)映射到一个整数序列,然后根据这个序列排序。
我找到的映射是这样的:
首先确认一个“很大的数”Big。由于数据库的时间范围在1970-01-01 ~ 2038-01-19之间,值小于pow(2, 31),所以我确定的“很大的数”是Big=pow(2, 40);
映射
f(start_time, end_time)
{
if (start_time < now < end_time)
{
return end_time;
}
else if (now < start_time)
{
return start_time + pow(2, 40);
}
else if (end_time < now)
{
return (-1)*end_time + pow(2,41);
}
}
自己证明:end_time < start_time + pow(2, 40) < (-1)*end_time + pow(2,41)
然后,写出的sql语句是:
SELECT id, start_time, end_time FROM lj_activity WHERE STATUS = 0 ORDER BY
IF (UNIX_TIMESTAMP(start_time)<=UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(NOW())<UNIX_TIMESTAMP(end_time),
UNIX_TIMESTAMP(end_time),
IF(UNIX_TIMESTAMP(NOW())<UNIX_TIMESTAMP(start_time),
UNIX_TIMESTAMP(start_time)+POW(2,40),
UNIX_TIMESTAMP(end_time)*(-1)+POW(2,41)
)
);