按照距离当前时间最近并且返回带有序号的sql语句

最新推荐文章于 2024-01-12 15:48:09 发布

原创最新推荐文章于 2024-01-12 15:48:09 发布 · 713 阅读

0 ·

CC 4.0 BY-SA版权

JAVA 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一个MySQL考试管理系统中用于查询考试记录的复杂SQL查询。该查询根据考试的状态进行排序，并在相同状态下按距离当前时间最近的考试优先显示。此外，还支持通过多种参数筛选考试记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql 考试管理，考试有不同的状态，首先根据不同的状态排序，在根据不同状态下根据距离当前时间最近的排序

 <select id="getSelectedIndex" resultMap="resultMap">
        SELECT *  FROM ( SELECT *  FROM ( select (@rank:=@rank+1) AS row_number , page_test.* FROM ( select
        <include refid="columnSql"></include>
        from exp_examine t
        <where>
            <if test="id != null  ">
                and t.ID = #{id}
            </if>
            <if test="name != null and name != ''  ">
                and t.NAME LIKE "%"#{name}"%"
            </if>
            <if test="startTime != null  ">
                and t.START_TIME = #{startTime}
            </if>
            <if test="endTime != null  ">
                and t.END_TIME = #{endTime}
            </if>
            <if test="subId != null  ">
                and t.SUB_ID = #{subId}
            </if>
            <if test="examId != null  ">
                and t.EXAM_ID = #{examId}
            </if>
            <if test="isDelete != null  ">
                and t.IS_DELETE = #{isDelete}
            </if>
            <if test="createTime != null  ">
                and t.CREATE_TIME = #{createTime}
            </if>
            <if test="createUser != null  ">
                and t.CREATE_USER = #{createUser}
            </if>
            <if test="status != null and status != ''  ">
                and t.STATUS = #{status}
            </if>
            <if test="remarkOnSpot != null  ">
                and t.REMARK_ON_SPOT = #{remarkOnSpot}
            </if>
        </where>

        ) page_test ,(SELECT @rank:=0) tt
        )  a ORDER BY CASE WHEN STATUS IS NULL THEN 1 WHEN STATUS = '10U' THEN 1 WHEN STATUS = '10R' THEN 1 ELSE 0 END DESC,
        abs(TIMESTAMPDIFF(SECOND,now(),START_TIME)) asc ) b


        <if test="page != null and page.orderBy != null and page.orderBy != ''">
            order by ${page.orderBy}
        </if>

    </select>