sql_上移_下移_置顶_置底

本文主要是介绍sql_上移_下移_置顶_置底，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

    <!--置顶-->
    <update id="moveTop" parameterType="com.hisign.oses.entity.os.Jcybxx">
        UPDATE
        <include refid="TableClause"/>
        SET XH = (CASE WHEN XH = #{xh} THEN 1 ELSE XH + 1 END)
        WHERE
        instr(
        (SELECT if(rn = 1, NULL, bhs) FROM (
        SELECT group_concat(BH) AS bhs, count(*) AS rn FROM (
        SELECT BH FROM
        <include refid="TableClause"/>
        WHERE
        WTZJBH = #{wtzjbh} AND XH <![CDATA[  <= ]]> #{xh} AND SCBJ = 0) t) t), BH) <![CDATA[  > ]]> 0
    </update>

    <!--上移-->
    <update id="moveUp" parameterType="com.hisign.oses.entity.os.Jcybxx">
        UPDATE
        <include refid="TableClause"/>
        SET XH = (CASE WHEN XH = #{xh} THEN #{xh} - 1 ELSE #{xh} END)
        WHERE
        instr(
        (SELECT if(rn = 2, bhs, NULL) FROM (
        SELECT group_concat(BH) AS bhs, count(*) AS rn FROM (
        SELECT BH FROM
        <include refid="TableClause"/>
        WHERE
        WTZJBH = #{wtzjbh} AND XH <![CDATA[  <= ]]> #{xh} AND SCBJ = 0 ORDER BY XH DESC LIMIT 2 ) t) t), BH) <![CDATA[  > ]]> 0
    </update>

    <!--下移-->
    <update id="moveDown" parameterType="com.hisign.oses.entity.os.Jcybxx">
        UPDATE
        <include refid="TableClause"/>
        SET XH = (CASE WHEN XH = #{xh} THEN #{xh} + 1 ELSE #{xh} END)
        WHERE
	    instr(
        (SELECT if(rn = 2, bhs, NULL) FROM (
		SELECT group_concat(BH) AS bhs, count(*) AS rn FROM (
		SELECT BH FROM
        <include refid="TableClause"/>
		WHERE
        WTZJBH = #{wtzjbh} AND XH <![CDATA[  >= ]]> #{xh} AND SCBJ = 0 ORDER BY XH LIMIT 2 ) t) t), BH) <![CDATA[  > ]]> 0
    </update>

    <!--置底-->
    <update id="moveBottom" parameterType="com.hisign.oses.entity.os.Jcybxx">
        UPDATE
        <include refid="TableClause"/>
        SET XH = (CASE WHEN XH = #{xh} THEN @maxord ELSE XH - 1 END)
        WHERE
        instr(
        (SELECT if(rn = 1, NULL, bhs) FROM (
        SELECT group_concat(BH) as bhs, count(*) AS rn, (SELECT @maxord:= (SELECT MAX(XH))) AS maxord FROM
        (SELECT BH,XH FROM
        <include refid="TableClause"/>
        WHERE WTZJBH = #{wtzjbh} AND XH <![CDATA[  >= ]]> #{xh} AND SCBJ = 0) t) t),BH) <![CDATA[  > ]]> 0
    </update>

这篇关于sql_上移_下移_置顶_置底的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！