需求是这样的: 我的考试列表分页查询。
1. 进行中的 end>now >start) 2. 待开始的(now <start) 3. 已结束(end<now)
SELECT t1.exam_id AS examId,t1.id AS id,t1.state AS examResult,t1.submit_time AS submitTime,t1.score AS score, t1.user_exam_mongo_id AS userExamMongoId, t1.anser_mongo_id AS anserMongoId,t1.answer_times AS answerTimes,t1.answer_id AS answerId,
t2.title AS title,t2.ended_at AS endedAt,t2.max_answer_time AS maxAnswerTime,t2.started_at AS startedAt,t2.max_answer_times AS maxAnswerTimes, t2.publish_result_state AS publishResultState
FROM mtk_exam_users t1 INNER JOIN mtk_exams t2 ON t2.id = t1.exam_id
WHERE t1.user_id = 95 AND t2.exam_type='official'
-- and (t2.title like %%)
-- and case when ?4 is null then 1=1 when ?4=1 then t2.started_at>?9 when ?4=2 then t2.started_at<=?9 and t2.ended_at>?9 when ?4=3 then t2.ended_at<=?9 end
-- and case when ?5 is null then 1=1 when ?5=2 then (t1.state=?5 or (t1.state in (3,4) and t2.publish_result_state=0)) else (t1.state=?5 and t2.publish_result_state=1) end
-- and (?6 is null or t2.category_id=?6)
-- and t1.is_delete=0 and t2.is_delete=0 and t2.release_state=1 and t2.file_state=0
ORDER BY CASE WHEN TO_DAYS(NOW()) BETWEEN TO_DAYS(t2.started_at) AND TO_DAYS(t2.ended_at) THEN 0 WHEN TO_DAYS(NOW()) < TO_DAYS(t2.started_at) THEN 1 ELSE 2 END , t2.started_at DESC
有个问题是对于今天的考试(还没开考的试卷)是倒序排列的,实际希望今天未开始考试的是按时间升序。有SQL大神的留言指点下。
实际希望的是:
今天待开始的(按start升序)>今天进行中的(按start降序)>除今天待开始的(按start降序)>已结束的(按start降序)