Mysql 分组查询 Case When

清疚 2022-12-10 14:39 110阅读 0赞

需求是这样的： 我的考试列表分页查询。

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降序）