//向上移动就把上一条数据和本条数据的排序字段交换下
UPDATE sys_custom_group AS a,
sys_custom_group AS b
SET a.seq = b.seq,
b.seq = a.seq
WHERE
a.id = '051d11d139794532963beb75f40c0afc' and
EXISTS(select * from (select id from sys_custom_group where seq < 9 and Parent_ID = '1a83c7ac73c54be8843ffa0b3b5ee550' order by seq desc limit 0,1) r
where r.id = b.id
)
后台只要获取,要移动的该条数据的id,和其顺序seq,我这里因为是tree形数据结构,所以还有个parentId;
--------------------------------------------------------------------------------------------
下面看在mybatis里怎么写;
区分flag isUp; 如果是向上移动为true 向下移动为true
<update id="upOrDownGroup" parameterType="map" >
UPDATE sys_custom_group AS a,
sys_custom_group AS b
SET a.seq = b.seq,
b.seq = a.seq
<if test="isUp == true ">
WHERE
a.id = #{sysCustomGroup.id,jdbcType=VARCHAR} and
EXISTS(select * from (select id from sys_custom_group where seq < #{sysCustomGroup.seq,jdbcType=INTEGER} and Parent_ID = #{sysCustomGroup.parentId,jdbcType=VARCHAR} order by seq desc limit 0,1) r
where r.id = b.id
)
</if>
<if test="isUp == false ">
WHERE
a.id = #{sysCustomGroup.id,jdbcType=VARCHAR} and
EXISTS(select * from (select id from sys_custom_group where seq > #{sysCustomGroup.seq,jdbcType=INTEGER} and Parent_ID = #{sysCustomGroup.parentId,jdbcType=VARCHAR} order by seq asc limit 0,1) r
where r.id = b.id
)
</if>
</update>