oracle上一篇，下一篇文章的sql语句

最新推荐文章于 2024-07-19 17:34:15 发布

wei9699pk

最新推荐文章于 2024-07-19 17:34:15 发布

阅读量106

点赞数

文章标签： sql oracle mybatis

本文链接：https://blog.csdn.net/wei9699pk/article/details/129926239

版权

java的mybatis

上一篇（传输当前文章的id，然后获取它的上一篇的id）

<select id="getPreviousArticle" resultType="java.lang.Long">
        SELECT
        m.id
        FROM
        (
        select t.*,rownum ROW_ID
        from (
        SELECT
        id
        FROM
        information
        <where>
            <if test="params.delFlag != null">
                and del_flag = #{params.delFlag}
            </if>
            <if test="params.showFlag != null">
                and show_flag = #{params.showFlag}
            </if>
        </where>
        <if test="params.orderField != null and params.orderField.trim() != '' and params.order != null and params.order.trim() != ''">
            order by ${params.orderField} ${params.order}
        </if>
        ) t
        ) m
        ,
        (
        SELECT
        c.*
        FROM
        (
        select t.*,rownum ROW_ID
        from (
        SELECT
        id
        FROM
        information
        <where>
            <if test="params.delFlag != null">
                and del_flag = #{params.delFlag}
            </if>
            <if test="params.showFlag != null">
                and show_flag = #{params.showFlag}
            </if>
        </where>
        <if test="params.orderField != null and params.orderField.trim() != '' and params.order != null and params.order.trim() != ''">
            order by ${params.orderField} ${params.order}
        </if>
        ) t
        ) c
        <where>
            c.id = #{params.id}
        </where>
        ) n
        WHERE
        m.ROW_ID &lt; n.ROW_ID and m.ROW_ID = n.ROW_ID-1
    </select>

下一篇（传输当前文章的id，然后获取它的下一篇的id）

<select id="getNextArticle" resultType="java.lang.Long">
        SELECT
        m.id
        FROM
        (
        select t.*,rownum ROW_ID
        from (
        SELECT
        id
        FROM
        information
        <where>
            <if test="params.delFlag != null">
                and del_flag = #{params.delFlag}
            </if>
            <if test="params.showFlag != null">
                and show_flag = #{params.showFlag}
            </if>
        </where>
        <if test="params.orderField != null and params.orderField.trim() != '' and params.order != null and params.order.trim() != ''">
            order by ${params.orderField} ${params.order}
        </if>
        ) t
        ) m
        ,
        (
        SELECT
        c.*
        FROM
        (
        select t.*,rownum ROW_ID
        from (
        SELECT
        id
        FROM
        information
        <where>
            <if test="params.delFlag != null">
                and del_flag = #{params.delFlag}
            </if>
            <if test="params.showFlag != null">
                and show_flag = #{params.showFlag}
            </if>
        </where>
        <if test="params.orderField != null and params.orderField.trim() != '' and params.order != null and params.order.trim() != ''">
            order by ${params.orderField} ${params.order}
        </if>
        ) t
        ) c
        <where>
            c.id = #{params.id}
        </where>
        ) n
        WHERE
        m.ROW_ID &gt; n.ROW_ID and m.ROW_ID = n.ROW_ID+1
    </select>